Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have multi-column index on col1,col2. Both columns are unsigned int with values 1 or greater. I want WHERE col2=100. I write WHERE col1>0 AND col2=100. The query should now use my index.

Does this work and what are the ramifications. How does it get optimized etc.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

It is doubtful that the optimizer would choose to use this index, and if it did, MySQL would need to do a complete index scan in order to match the second entry. This would be less efficient than a table scan.

Add an index on col2.

EDIT 7/11/2012

It occurs to me that an analogy might help you understand why this can't possibly work.

Consider that an index is sort of like a phone book from ye olde times. Phone books have a compound index on (lastname, firstname).

This is a phone book:

CREATE TABLE phonebook (
  lastname varchar(255) NOT NULL,
  firstname varchar(255) NOT NULL,
  phone varchar(11) NOT NULL,
  PRIMARY KEY (lastname,firstname)
)

If you want to find my number in the book, you would find Brown, Aaron in the book which would then have the data that you want (my number). If you want to find all people with the last name 'Brown', it's also easy because they are organized by lastname - you just find the first 'Brown' entry and keep going until you are no longer looking at "Brown's." So, this works:

SELECT phone FROM phonebook WHERE lastname = 'Brown';

or even

SELECT phone FROM phonebook WHERE lastname LIKE 'B%';

What if you want to find everyone named "Aaron?" You can't use the index that the phone book provides - you have to look at every single entry in the book in order to get all the Aarons.

-- this will still have to scan every record in the table
SELECT phone FROM phonebook WHERE lastname >= 'A' AND firstname = 'Aaron';

In your example, col1 is lastname and col2 is firstname. You are trying to trick MySQL into using a phone book index by saying "Give me all the people with the first name of Aaron that also have a last name that starts with a letter." Knowing that the last name starts with a letter doesn't help you find all the Aarons, just like knowing that col1 > 0 doesn't help. You still have to look at every entry in the table.

share|improve this answer
    
@Dor1000: There is no trick that can trick the optimizer to use this mutli-column index for this query. Add the simple index on (col2), as Aaron suggests and your problem is solved. –  ypercube Jul 11 '12 at 8:01
    
The example is great. But I wouldn't say that "You can't use the index that the phone book provides" You can use it, but you have to look up on every single lastname entry (and check if there is a sub-entry, named 'Aaron'). –  ypercube Jul 11 '12 at 14:45
    
This is a great answer that starts out simple and illustrates well. I dare say, this explains the whole left prefix rule does it not. Thanks everyone for help. –  Dor1000 Jul 13 '12 at 6:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.