MySQL Forums
Forum List  »  InnoDB

Slow query log 'Rows_examined' vs 'explain select' rows
Posted by: Ramon Rockx
Date: January 09, 2013 05:26AM

Hello,

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
SET timestamp=1357683797;
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:

id: 1
select_type: SIMPLE
table: visitorsession
type: index
possible_keys: indx_dateexpired,indx_datelastvisited
key: PRIMARY
key_len: 8
rows: 147128
ref: NULL
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.
Thanks!

Regards,
Ramon Rockx

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query log 'Rows_examined' vs 'explain select' rows
9899
January 09, 2013 05:26AM


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.