Performance Problem SELECT with FORCE INDEX

[coller]

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
Key_len:4
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!

[sterin]

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.

[Peter]

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.