MySQL Forums :: InnoDB :: can mysql use 2 different execution plans for the same query ?


Advanced Search

can mysql use 2 different execution plans for the same query ?
Posted by: Cyril SCETBON ()
Date: May 12, 2011 04:12AM

Hi,

I make a procedure call in MySQL which queries views and InnoDB-only tables.
However between 2 executions, I get different execution duration (30 seconds versus 900 seconds) and see different informations in the slow query log :

# Query_time: 28.174426 Lock_time: 0.000241 Rows_sent: 8431
Rows_examined: 23493713
SET timestamp=1305192292;
CALL
assigneeHistoryWithAppNumberList('XXX');

# Query_time: 922.236188 Lock_time: 0.000000 Rows_sent: 8431
Rows_examined: 1199433363
use jrep_data_prd;
SET timestamp=1305132214;
CALL
assigneeHistoryWithAppNumberList('XXX');

where XXX is string of 1000 characters.

Up to now, I can't reproduce it, but as tables weren't modified, how can MySQL use 2 differents plan for the same query ?

Do you think it can be caused by the sampling used for updating statistics ? I thought theses statistics was updated only on modifications ?

thanks

Options: ReplyQuote


Subject Views Written By Posted
can mysql use 2 different execution plans for the same query ? 2435 Cyril SCETBON 05/12/2011 04:12AM
Re: can mysql use 2 different execution plans for the same query ? 1276 Rick James 05/13/2011 09:02AM
Re: can mysql use 2 different execution plans for the same query ? 692 Cyril SCETBON 05/13/2011 09:09AM


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.