mysql dummy where check to utilize multi column index 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&gt;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.
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 &gt;= '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 &gt; 0 doesn't help.  You still have to look at every entry in the table.