MySQL Bugs: #16459: log-queries-not-using-indexes should not report queries on ENUM()s

Bug #16459	log-queries-not-using-indexes should not report queries on ENUM()s
Submitted:	12 Jan 2006 16:45	Modified:	17 Feb 2006 14:07
Reporter:	Cyril Bouthors	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	4.1 and later	OS:	Linux (Debian GNU/Linux)
Assigned to:		CPU Architecture:	Any

Description:
log-queries-not-using-indexes should not report queries on ENUM()s because they are indexed.

"Each enumeration value has an index" according to the manual at http://dev.mysql.com/doc/refman/4.1/en/enum.html and http://dev.mysql.com/doc/refman/5.0/en/enum.html

How to repeat:
Put this in your MySQL configuration:

log-queries-not-using-indexes
log-slow-queries = /var/log/mysql/slow-queries.log

Then tell MySQL to run this:

CREATE TABLE `test` (
`foo` ENUM( 'a', 'b' ) NOT NULL
) TYPE = MYISAM ;

INSERT INTO test SET foo='a';
INSERT INTO test SET foo='b';

select * from test where foo='a';

And now, if you have a look at /var/log/mysql/slow-queries.log you'll see:

# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 2
use test;
select * from test where foo='a';

but this query should not be reported as slow because the ENUM() called 'foo' is indexed.

Thank you for a problem report. Sorry, but you misinterpreted tha manual. It says about index as in array, array[1] is the first element, array[2] is the second etc. Please, read on the pages you quoted:

"Each enumeration value has an index:
-      Values from the list of allowable elements in the column specification are numbered beginning with 1.
-      The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:

mysql> SELECT * FROM tbl_name WHERE enum_col=0;
-      The index of the NULL value is NULL.
..."

This only mean that each value of ENUM can be "represented" by number, starting with 1. Look:

mysql> CREATE TABLE `test` (
    -> `foo` ENUM( 'a', 'b' ) NOT NULL
    -> ) TYPE = MYISAM ;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO test SET foo='a';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test SET foo='b';
Query OK, 1 row affected (0.08 sec)

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

mysql> show create table test;
+-------+-----------------------------------------------------------------------
----------------------+
| Table | Create Table
                      |
+-------+-----------------------------------------------------------------------
----------------------+
| test  | CREATE TABLE `test` (
  `foo` enum('a','b') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
----------------------+
1 row in set (0.01 sec)

There is clearly no keys in your table (no indexes to be used by SELECTs). But you can do the following:

mysql> select * from test where foo='a';
+-----+
| foo |
+-----+
| a   |
+-----+
1 row in set (0.00 sec)

mysql> select * from test where foo=1;
+-----+
| foo |
+-----+
| a   |
+-----+
1 row in set (0.00 sec)

That "1" is the index of 'a' in your ENUM.

Valery,

I'm sorry for this misinterpretation of the manual.

This is indeed not a bug in the code but I think it's a bug in the manual and it should be fixed in order to avoid misinterpretations, I'm not the only one who have made this mistake here... :-)

I'm re-opening this bug, assigning it to the documentation section and switching the severity level to S4-feature.

Thanks.

What exact sentence in the manual is not clear? "Each enumeration value has an index"? How can one interpret that as "index" in the database sense (a separate structure to speed up key values search)? And what kind of fix can you propose?

Sorry, but the manual looks absolutely clear for me in this part.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".