CODEWITHSUNDEEP
mysqlsqldatabaseindexing
Leem.fin
Leem.fin

Reputation: 42602

"WHERE ... IN ..." , "WHERE ... != ..." query indexing issue

I am using MySQl database.

I know if I create a index for a column, it will be fast to query data from a table by using that column index. But, I still have the following questions:

(suppose I have a table named cars, there is a column named country, and I have created index for country column)

  1. I know for example the query SELECT * FROM cars WHERE country='japan'will use the index on column country to query data which is fast. How about != operation? will SELECT * FROM cars WHERE country!='japan'; also use index to query data?

  2. Does WHERE ... IN ... operation use index to query data? For example SELECT * FROM cars WHERE country IN ('japan','usa','sweden');

Upvotes: 3

Views: 304

Answers (4)

Gustav Bertram
Gustav Bertram

Reputation: 14911

You can use EXPLAIN SELECT to find out if your query uses an index or not.

For example:

EXPLAIN SELECT * 
FROM A 
WHERE foo NOT IN (1,4,5,6);

Might yield:

+----+-------------+-------+------+---------------
| id | select_type | table | type | possible_keys 
+----+-------------+-------+------+---------------
|  1 | SIMPLE      | A     | ALL  | NULL          
+----+-------------+-------+------+---------------

+------+---------+------+------+-------------+
| key  | key_len | ref  | rows | Extra       |
+------+---------+------+------+-------------+
| NULL | NULL    | NULL |    2 | Using where |
+------+---------+------+------+-------------+

In this case, the query had no possible_keys and therefore used no key to do the query. It's the key column you'd be interested in.

More information here:

Upvotes: 2

Bluewind
Bluewind

Reputation: 1064

Use 'EXPLAIN' to see what happens with your query. You will probably be interested in the 'possible_keys' and 'key' column.

EXPLAIN SELECT * FROM CARS WHERE `country` != 'japan'

Upvotes: 0

Benoit
Benoit

Reputation: 79185

The general answer is: it depends. It depends on what the database optimizer thinks is the best way to retrieve the data, and its decision may need on the distribution of the data.

For example, if 99% of your rows have country = 'japan', maybe the first query (=) will not use the index, but the country with != will use it.

Upvotes: 3

Mchl
Mchl

Reputation: 62387

Both queries will use indexes (assuming there is index with country as it's first column)

When in doubt use EXPLAIN. You will also want to read (at least parts of) this

Upvotes: -1

Related Questions