MySQL Bugs: #55979: Key not used if IN() list contains mix of strings and integers

Bug #55979	Key not used if IN() list contains mix of strings and integers
Submitted:	13 Aug 2010 22:05	Modified:	16 Aug 2010 8:44
Reporter:	Bruno Barberi Gnecco	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.1.39	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	performance

Description:
Updates are terribly slow if "IN()" is used and one of the keys is not quoted. See the comparison below. The two queries below are exactly the same, except the last item of the second one is not quoted. One is instantaneous, the other takes 13.77 seconds.

For reference, `id` int(10) unsigned NOT NULL AUTO_INCREMENT is a primary key.

How to repeat:
mysql> UPDATE table SET totalhits=totalhits+1 WHERE id IN ('860978', '2153414', '4453911', '9629', '535192', '382793', '35250', '633318', '736623', '102300', '120998', '13874', '358704', '1684418', '8144', '5009048', '5009037', '5009032', '5009030', '5009028', '4454461', '5009020', '5009021', '5009022', '5009050', '5009050', '5009022', '5009021', '5009020', '4454461', '1097893', '4326179', '1026400', '1842312', '2151960', '4326179', '23', '28483', '5832');
Query OK, 33 rows affected (0.00 sec)
Rows matched: 33  Changed: 33  Warnings: 0

mysql> UPDATE table SET totalhits=totalhits+1 WHERE id IN ('860978', '2153414', '4453911', '9629', '535192', '382793', '35250', '633318', '736623', '102300', '120998', '13874', '358704', '1684418', '8144', '5009048', '5009037', '5009032', '5009030', '5009028', '4454461', '5009020', '5009021', '5009022', '5009050', '5009050', '5009022', '5009021', '5009020', '4454461', '1097893', '4326179', '1026400', '1842312', '2151960', '4326179', '23', '28483', 5832);
Query OK, 33 rows affected (13.77 sec)
Rows matched: 33  Changed: 33  Warnings: 0

Bruno, don't use strings when your primary key is an integer. Remove all of the quotes.

Also hits SELECTS, so i took the freedom to update the bug synopsis.

How to reproduce easily:

  DROP TABLE IF EXISTS t1;

  CREATE TABLE `t1` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `totalhits` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  );

  INSERT INTO t1 SELECT NULL, 0;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1; 
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;
  INSERT INTO t1 SELECT NULL, 0 FROM t1;

  EXPLAIN SELECT * FROM t1 WHERE id IN (1,2);
  EXPLAIN SELECT * FROM t1 WHERE id IN ('1','2');
  EXPLAIN SELECT * FROM t1 WHERE id IN (1,'2');

Result:

  mysql> EXPLAIN SELECT * FROM t1 WHERE id IN (1,2);
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  |  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  1 row in set (0.00 sec)

  mysql> EXPLAIN SELECT * FROM t1 WHERE id IN ('1','2');
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  |  1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
  +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  1 row in set (0.00 sec)

  mysql> EXPLAIN SELECT * FROM t1 WHERE id IN (1,'2');
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  |  1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL | 1024 | Using where |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  1 row in set (0.00 sec)

So the primary key on the integer column `id` can be used as index if the values in the IN() list are either all integers or all strings, but not when mixing integers and strings.

Category "Performance schema" is for the performance schema (5.5),
not for general performance issues.

Changing category to MySQL server / optimizer