Slow query log 'Rows_examined' vs 'explain select' rows

[Ramon Rockx]

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