Avoided filesort, but more rows to exmaine, need advice!

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I am pretty new in optimizing tables with index and may need some help.
This is my query:

EXPLAIN SELECT timestamp
FROM Meting_INT_COPY
WHERE blockid = ‘200811252000’
ORDER BY timestamp DESC
LIMIT 1

If I have an index(blockid),
EXPLAIN will return the following information:

type possible_keys key rows Extra
ref index_blockid index_blockid 2638 Using where; Using filesort

If I add an index(blockid,timestamp)
EXPLAIN will display the following:

type possible_keys key
rows Extra
ref index_blockid,index_blockid_timestamp index_blockid_timestamp 8248
Using where; Using index

The index(blockid,timestamp) avoid the filesort + returns the result from index ! (Using where; Using index)
But why for the index(blockid) 2638 rows are returned to examine and for a more
specific index(blockid,timestamp) 8248 rows are returned ?

Thank you very much for any answer !

2

If you run the query without the LIMIT 1 at the end, I bet you would see more row examined without the additional index. It just so happens that the non-indexed execution is stumbling upon a matching row early into its search.