Fastest MySQL operator checking for not 0 in indexed columns: > or !=

What would be the fastest way to check for not 0 in an indexed column, using:

WHERE column != 0

or

WHERE column > 0

Do one of the above work better with indexed columns, or are they pretty much the same with regards to开发者_运维知识库 speed?

I've did this on a ~650k rows table, the column column is indexed. It seems there are slight differences, and indeed > 0 is faster. The two queries were run on two identical databases, so no cache could mess with the execution times.

explain
select * from table where column > 0;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  table   range   column  column  5   \N  4736    Using where

Execution Time : 00:00:00:005

explain
select * from table where column != 0;

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  table   range   column  column  5   \N  4746    Using where

Execution Time : 00:00:00:012

T think WHERE column > 0 will work faster.

Don't optimize a single condition, optimize whole statement.

If it's INTEGER UNSIGNED, both conditions should work in the same way.

If '>' compares by checking first occurrence of 1 bit from left to right, opposite to '!=' checking bit by bit from right to left, the first one would be faster. At least I think so...

It depends how "column" values are stored in hard disk.

If column entries are sorted in ascending order and values start from zero then both have same speed because mysql knows that in this special case both statements are equivalent..

If column values are not sorted in hard disk then it depends what index technique you have used..

For example B+ tree indexing or hash index? In this latter case B+ tree still computes both queries at same speed while hash indexing is faster with '>' than '!='

You really do think that a simple integer comparison will make much of a difference? If this is your application's bottleneck you pretty much done with optimizing I guess...