MySQL Forums
Forum List  »  Optimizer & Parser

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

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Not using index optimal
4043
May 14, 2011 01:02PM
1093
May 14, 2011 01:33PM
1227
May 15, 2011 01:57PM
1127
May 16, 2011 03:23AM
1231
May 16, 2011 06:41AM
1119
May 15, 2011 11:25PM
1086
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.