| Avoided filesort, but more rows to exmaine, need advice! [message #3743] |
Wed, 26 November 2008 16:54  |
 matthew016
Messages: 4
Registered: November 2008
Location: Brussels |
Junior Member |
|
|
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 !
|
|
|
| Re: Avoided filesort, but more rows to exmaine, need advice! [message #4140 is a reply to message #3743 ] |
Sat, 21 March 2009 21:18  |
MarkRose
Messages: 94
Registered: January 2008 |
Member |
|
|
|
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.
|
|
|
Members
Search
Help
Register
Login
Home
