MySQL Bugs: #55248: constant WHERE inefficient

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	constant WHERE inefficient

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);

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.