strange “using where” in simple query

Posted on

Question :

I have a table with this structure:

mysql> describe user_groups;

+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | MUL | NULL    | auto_increment |
| user_id  | int(11) unsigned     | NO   | PRI | NULL    |                |
| group_id | smallint(5) unsigned | NO   | PRI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

primary key of this table is : PRIMARY(user_id, group_id)
I have simple query:

mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserGroup | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

it has using where; , according it has additional disk seek,I know using where; concept, and Also I know it is not terrible , but conceptually I want to know why this query has using where;, although the condition is on primary key directly and it doesn’t have order or anything that causes using where;.
How can I fix this using where;?

Answer :

See the documentation.

Using index

The column information is retrieved from the table using only
information in the index tree without having to do an additional seek
to read the actual row. This strategy can be used when the query uses
only columns that are part of a single index.

If the Extra column also says Using where, it means the index is being
used to perform lookups of key values.
Without Using where, the
optimizer may be reading the index to avoid reading data rows but not
using it for lookups. For example, if the index is a covering index
for the query, the optimizer may scan it without using it for lookups.

In short, your query is working as intended and is not performing an additional disk seek.

Leave a Reply

Your email address will not be published. Required fields are marked *