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
Triage: Triaged: D3 (Medium)

[13 Aug 2010 22:05] Bruno Barberi Gnecco
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
[14 Aug 2010 11:18] James Day
Bruno, don't use strings when your primary key is an integer. Remove all of the quotes.
[16 Aug 2010 8:44] Hartmut Holzgraefe
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.
[23 Aug 2010 16:08] Marc Alff
Category "Performance schema" is for the performance schema (5.5),
not for general performance issues.

Changing category to MySQL server / optimizer