MySQL Forums :: InnoDB :: Slow query log 'Rows_examined' vs 'explain select' rows
Slow query log 'Rows_examined' vs 'explain select' rows
Posted by: Ramon Rockx
Date: January 09, 2013 05:26AM
I'm wondering if someone can help me out with the following thing which is unclear to me:
On our production environment we are using a large table (157 million records, MySQL 5.5, InnoDB). The slow query log is turned on, and although the following query is executed a lot (once every 10 seconds) a few times a day the query will pop up in this log, with stats simular like these:
# Query_time: 742.048022 Lock_time: 0.000071 Rows_sent: 42 Rows_examined: 157244068
select vss_id from visitorsession where (vss_dateexpired is null) and vss_datelastvisited<'2013-01-08 23:07:55' and vss_datelastvisited>'2013-01-08 21:10:55' order by vss_id desc limit 100;
As you can see the query takes a long time, but that's not so strange since it looks like a full table scan (see rows_examined).
However, when I query the following:
explain select vss_id from visitorsession where (vss_dateexpired is null) and vss_datelastvisited<'2013-01-08 23:07:55' and vss_datelastvisited>'2013-01-08 21:10:55' order by vss_id desc limit 100;
this results in:
Extra: Using where
So, as you can see 'rows' is a lot smaller than Rows_examined in the slow query log.
What I expected was that these values should be a lot closer to each other. And why the table scan?
I hope somebody can help me out.
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.