Announcement Module
No announcement yet.

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

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

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


    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.