# Performance Problem SELECT with FORCE INDEX

**URL:** <https://forums.percona.com/t/performance-problem-select-with-force-index/293>\
**Category:** Other MySQL® Questions\
**Created:** [April 19, 2007, 11:42am UTC](https://forums.percona.com/t/performance-problem-select-with-force-index/293 "2007-04-19T11:42:47Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![coller](https://avatars.discourse-cdn.com/v4/letter/c/e19b73/32.png) [@coller](https://forums.percona.com/u/coller)\
**Post date:** [April 19, 2007, 11:42am UTC](https://forums.percona.com/t/performance-problem-select-with-force-index/293/1 "2007-04-19T11:42:47Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [April 20, 2007, 2:06am UTC](https://forums.percona.com/t/performance-problem-select-with-force-index/293/2 "2007-04-20T02:06:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [April 20, 2007, 4:35pm UTC](https://forums.percona.com/t/performance-problem-select-with-force-index/293/3 "2007-04-20T16:35:57Z")

</div>

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.