MySQL Forums
Forum List  »  Performance

MySQL query execution time differs between executions
Posted by: Ofer Bechar
Date: November 16, 2014 09:49AM

I'm using MySQL 5.6.19 community edition and I have a large table (about 20,000,000 rows, about 250,000 inserts per day). The engine I'm using is InnoDB.

I'm running a query that joins to other table, filtering and sorting the data according to a specific criteria. I've created an index for this query because this query can run many times.

In addition, I created a routine that runs every night and "shrinks" the table. It find which rows can be deleted and which rows should be updated according to the business logic and it deletes about 150,000 rows every night and updates few thousands.

Now here's the questions:

After the stored routine finishes, the query execution time is lightning fast (milliseconds) but if the routine didn't run in a long period of time or right after a DB restart, the same query takes 20 seconds or more. The execution plan looks exactly the same when it is fast and when it is slow - here's the execution plan:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stationimp1_ ref_or_null PRIMARY,idx_station_complex_1 idx_station_complex_1 2 const 55 Using where; Using index; Using temporary; Using filesort
1 SIMPLE stationeve0_ ref idx_station_event_log_1,idx_station_event_log_complex_1 idx_station_event_log_complex_1 9 DMS.stationimp1_.id 575 Using index condition

The question is why do I have such a huge difference between the execution times and how could I make it faster even if the routine didn't run?

Little more on the routine: the routine inserts the latest rows (since the routine's last run) to a temporary table, doing all the business logic on the temporary table, inserts the id's I want to delete to another table and the id's I want to update to another table, then it deletes and updates in the original big table one by one accordingly.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL query execution time differs between executions
3463
November 16, 2014 09:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.