Bug #55248 constant WHERE inefficient
Submitted: 14 Jul 2010 9:22 Modified: 14 Jul 2010 11:17
Reporter: Alex Zimnitski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.1.43, 5.1.48 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: constant WHERE inefficient

[14 Jul 2010 9:22] Alex Zimnitski
Description:
constant condition in WHERE - inefficient optimize

analyzing 5 rows, i expected 3, like

EXPLAIN SELECT id,gr FROM tt
WHERE gr IN (1,2);

How to repeat:
CREATE TABLE tt (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  gr int(10) unsigned NOT NULL,
  dsc varchar(200) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY gr (gr,id)
) ENGINE=MyISAM;
INSERT INTO `tt` VALUES (1,1,'a'),(2,1,'a'),(3,2,'b'),(4,2,'b'),(5,3,'c'),(6,3,'c'),(7,4,'d'),(8,4,'d');

EXPLAIN SELECT id,gr FROM tt
WHERE gr IN (1,2,4) AND
IF(1=1, gr!=4, 0);
[14 Jul 2010 11:17] Valeriy Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE tt (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   gr int(10) unsigned NOT NULL,
    ->   dsc varchar(200) NOT NULL,
    ->   PRIMARY KEY (id),
    ->   UNIQUE KEY gr (gr,id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO `tt` VALUES
    -> (1,1,'a'),(2,1,'a'),(3,2,'b'),(4,2,'b'),(5,3,'c'),(6,3,'c'),(7,4,'d'),(8,
4,'d');
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id,gr FROM tt
    -> WHERE gr IN (1,2);
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
|  1 | SIMPLE      | tt    | range | gr            | gr   | 4       | NULL |
3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
1 row in set (0.06 sec)

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

mysql> EXPLAIN SELECT id,gr FROM tt
    -> WHERE gr IN (1,2,4) AND
    -> IF(1=1, gr!=4, 0);
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
|  1 | SIMPLE      | tt    | range | gr            | gr   | 4       | NULL |
5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+--------------------------+
1 row in set (0.01 sec)

It's a pity that optimizer does not understand that additional IF(...) condition eliminates 4 from possible column values effectively. It would be nice for optimizer to evaluate such constant expressions and simplify WHERE clause.