MySQL slow query log and explain don't agree

MySQL & MariaDB Other MySQL® Questions
1

I’ve been able to use the slow query log to spot poor performing queries. I have then used explain to identify the problem and improve upon the queries.

However …

Today, the slow query log and explain do not agree. For example:

I see an entry like the following:

Query_time: 55 Lock_time: 24 Rows_sent: 0 Rows_examined: 489622

I take the query and run explain and it is perfectly well tuned, examining only a few 100 rows (according to explain) and even runs in a couple hundred ms when running the query stand alone.

Any ideas ??

2

What Storageengine do you use?

Rows examined in explain, is a guess of MySQL… how many rows it has to retrive from the storageengine, after evaluating the indexes.

This guess is based on statistics about the indexes… But this statistic data is not always up to date (run optimzie query).
And if the values are up to date, it is still statistic data. The actual amount of rows to evaluate to run the query might be different…