Not using index optimal

[Per Steffensen]

Hi

I have a table X with columns pid (primary/unique), a, b and c. I have an btree index (X_a_b_c_pid_index) on columns a,b,c,pid in that order.

Query: SELECT * FROM X USE INDEX (X_a_b_c_pid_index) WHERE a=3 AND b='ACTIVE' ORDER BY c, pid LIMIT 0, 100

I believe MySQL should be very efficient answering this query, because it can use the index for everything (WHERE, ORDER BY and LIMIT). Basically it should just find the top row in the index with a=3 and b='ACTIVE' and then take the next 100 rows in the index from there. I believe it only needs to visit 100 rows.

I have 25000 rows in table X all with a=3 and b='ACTIVE'. pid is unique. I get "poor" performance from the query (seconds instead of splitseconds :-) )

When I explain the query I get the following:
---------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------
| 1 | SIMPLE | X | ref | X_a_b_c_pid_index | X_a_b_c_pid_index | 71 | const,const | 12593 | Using where |
---------------------------------------------------------------------------------

It seems to me that i visits 12593 rows. Why? I dont know if "Using where" in "Extra" indicates that the index is only used for WHERE (and not ORDER BY and LIMIT)

Anyone? Thanks!

Regards, Per Steffensen