Announcement Module
No announcement yet.

Performance Problem SELECT with FORCE INDEX

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

  • Performance Problem SELECT with FORCE INDEX

    I have the following performance problem with a select statement. The table is about 651.000 entries. The statement looks like:

    SELECT SQL_CACHE ID, filename,
    FROM table
    FORCE INDEX ( ID_2 )
    WHERE ID > 90000
    ORDER BY page_rank DESC
    LIMIT 245 , 15

    The index ID_2 is:

    Name:ID_2, Typ: INDEX, Cardinality: 651002, Fields: ID, page_rank

    The request last very long, about 45 secs. The EXPLAIn shows the following:

    table: table
    type: range
    possible_keys: ID_2
    key: ID_2
    ref: NULL
    rows: 651002
    Extra: Using where; Using filesort

    What do I wrong? Why does mysql run through the hole table? Why "Using filesort" even I use the index ID_2??

    Please help! Thank in advance!

  • #2
    It's not running thru the whole table.

    The "range" indicates that it performs a range scan of the index.

    The "using filesort" has to do with your ORDER BY part of the query.

    Some thoughts:
    How many rows does the >90000 match?
    Remember that all these rows has to be sorted due to your ORDER BY page_rank.

    Create a combined index on (ID, page_rank) that way MySQL may use that to solve the sorting.

    And as a last resort if you can't limit it better in the WHERE clause then above didn't help you, increase your sort_buffer_size because when that buffer is full mysql writes a temporary table to disk which slows down the query a lot.


    • #3
      You have index on (ID,page_rank) and ordering on page_rank

      MySQL can't use index for order by in such case.

      If ID > 90000 is not very selective simply index on page_rank will work better.


      • #4
        Peter wrote on Sat, 21 April 2007 00:05

        You have index on (ID,page_rank) and ordering on page_rank

        MySQL can't use index for order by in such case.

        Right, sorry it can only use it when ID = const. Not ID < or > something.