Optimization SQL Query For Analytics

I have implemented analytics system which is now performing very poorly. To explain it I need to explain table structure queries

I have two innodb tables

Table1: Contains records about hourly stats (stats_id, file_id, time) Table2: Contains over 8 million rows.

Table 2 structure is

full_stats (
    stats_id Int
    file_id Int
    stats_week Int
    stats_month Int
    stats_year Int
    stats_time DATETIME

)

What I am trying to do is to calculate the total views from hourly_stats for a given period of time and grouping records by file_id and then I add/update records to full_stats table. On avg it takes 1-2 mins to process one row. I am trying to optimize the queries for better performance.

Here is what I am doing

There are 60% chances that file_id already exists in full_stats for a given week, month and year and 40% chances are that it doesn't exist.

so in the first query I try to update record using following the query

UPDATE full_stats 
   SET total_views=XXX 
 WHERE stats_week=XX stats_month=X 
   AND stats_year=YYYY

after that I check if affected rows is zero then I insert the record. Once insert or update is done then the record from hourly_stats is removed based on file_id and the given period of time.

Can you give me any suggestion how to optimize queries and reduce the lock rate?

ansaurus

tags:

views:

answers:

Optimization SQL Query For Analytics

related questions