Ad

Does MySql DBMS Change Queries To Use Index Before They Execute It?

- 1 answer

When I query, using '!=' or '<>' I know then the DBMS does not use indexes.

select * from test where test_id != 1234

So querying like bottom, you can have the same result using indexes.

select * from test where test_id < 1234 or test_id > 1234

But when I see the explain plan both uses range(range scan). So I'm curious about, if I query using '!=' does MySql DBMS change the query like '> and <' to use the index.

Ad

Answer

!= (aka <>) usually means "I want all rows except for one". So having an index on that column is essentially useless; it is faster to scan the table, skipping over that one row.

WHERE test_id < 1234 OR test_id > 1234 

is equivalent to

WHERE test_id != 1234

But OR usually cannot use any index.

Ad
source: stackoverflow.com
Ad