MySQL Forums
Forum List  »  Optimizer & Parser

Not using index optimal
Posted by: Per Steffensen
Date: May 14, 2011 01:02PM


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

Options: ReplyQuote

Written By
Not using index optimal
May 14, 2011 01:02PM
May 14, 2011 01:33PM
May 15, 2011 01:57PM
May 16, 2011 03:23AM
May 16, 2011 06:41AM
May 15, 2011 11:25PM
May 16, 2011 03:18AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.