Can't hit the index when a variable in SQL?

Question

Following SQL can't hit the index idx_user_userid, and I don't know how to solve it?

SET @q = 'abcd';
EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = @q;

user description:

CREATE user(
  row_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  user_id CHAR(20) NOT NULL,
  mobile_num CHAR(15) NOT NULL DEFAULT ''
) ENGINE = InnoDB
CREATE UNIQUE INDEX idx_user_userid ON user(user_id);

MySQL version is 5.1.36

Answer 1

This works as I would expect in MySQL 5.0.51a-24. Do any rows in your 'user_id' column actually contain the value that is in your @q variable? Here's the output from my system:

CREATE TABLE `user` (
    `row_id` int(11) NOT NULL auto_increment,
    `user_id` char(20) NOT NULL,
    `mobile_num` char(15) NOT NULL default '',
    PRIMARY KEY  (`row_id`),
    UNIQUE KEY `idx_user_userid` (`user_id`)
) ENGINE=InnoDB;

INSERT INTO `user` VALUES
    (1, 'user1', '1234567890'),
    (2, 'user2', '1234567890');

SELECT * FROM `user`;

+--------+---------+------------+
| row_id | user_id | mobile_num |
+--------+---------+------------+
|      1 | user1   | 1234567890 |
|      2 | user2   | 1234567890 |
+--------+---------+------------+

SET @q = 'user1';

EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = @q;

+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | idx_user_userid | idx_user_userid | 20      | const |    1 |       |
+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+

SET @q = 'abcd';

EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = @q;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Note how an 'Impossible WHERE' message is returned by the EXPLAIN SELECT statement when the value 'abcd' is used. This happens because MySQL is unable to match the WHERE statement, as the supplied value is not present in the table. However, when a valid value is supplied, the correct index is selected.

The same result is returned if you use literal values instead of passing a user defined variable:

EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = 'abcd';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

EXPLAIN SELECT user_id, mobile_num FROM user WHERE user_id = 'user1';
+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | idx_user_userid | idx_user_userid | 20      | const |    1 |       |
+----+-------------+-------+-------+-----------------+-----------------+---------+-------+------+-------+