Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using enum as an alternative to check constraint which is still not supported by mysql. Could you clarify why optimizer doesn't take into account allowed values for enum ? For example,

CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
insert into test_1(val) values ('A');
1. explain select * from test_1 where val is null; 
2. explain select * from test_1 where val ='C';

The first explain shows impossible where in Extra, but the second doesn't. There is no way val can contain 'C' (as well as null), and it can be deducted from table definition without looking into the table.

Thanks.

share|improve this question
    
+1 for bringing this type of issue public !!! –  RolandoMySQLDBA Jan 12 '12 at 19:10

1 Answer 1

up vote 1 down vote accepted

I create the sample you gave with this:

drop database if exists a1ex07;
create database a1ex07;
use a1ex07
CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null); 
insert into test_1(val) values ('A'); 
explain select * from test_1 where val is null;  
explain select * from test_1 where val ='C'; 

Here is it loaded:

mysql> drop database if exists a1ex07;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create database a1ex07;
Query OK, 1 row affected (0.00 sec)

mysql> use a1ex07
Database changed
mysql> CREATE TABLE test_1(id int not null auto_increment PRIMARY KEY, val enum('A','B') not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_1(val) values ('A');
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from test_1 where val is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_1 where val ='C';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test_1 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>

In the first EXPLAIN plan, the Impossible where would be based on the definition of the column you gave, which is val enum('A','B') not null. This would help the MySQL Query Optimizer quickly rule out unneccesary evaluations, especially if the table had millions of rows.

In the second EXPLAIN plan, the Using where indicates that MySQL Query Optimizer would have to evaluate a potential value (C) to see if it resides in the table. I believe what you are looking for is for the MySQL Query Optimizer to check the definition of the column, which is enum('A','B'). It should but it probably doesn't. You should submit that as a bug report to see if Oracle, out of the goodness of their heart, will address this.

UPDATE 2012-01-12 14:08 EDT

I just looked around mysql website for any bugs regarding EXPLAIN and ENUM. At this time, there are no reported bugs of ths nature. You should submit this one ASAP.

share|improve this answer
    
Thanks, I'll do that (submit the bug). I was not sure if it is a bug or feature... –  a1ex07 Jan 12 '12 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.