MySQL Bugs: #61267: Index Prefix Length Completely Ignored

Bug #61267	Index Prefix Length Completely Ignored
Submitted:	23 May 2011 18:18	Modified:	14 Jul 2011 18:53
Reporter:	Dustin Oprea	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.5.12	OS:	Windows (64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, index prefix length, prefix length

Description:
I've tried in the native version of two different Linux systems, Windows 5.1.42 64-bit, and Windows 5.5.12 64-bit. I've also tried different lengths, but the commands below never produces the intended result. It might be just that it's not described correctly in the SHOW CREATE TABLE result.

How to repeat:
mysql> CREATE TABLE `TestIDX2` (`SomeValue` VARCHAR(200) NOT NULL, INDEX(`SomeValue`(200))) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> SHOW CREATE TABLE `TestIDX2`;
+----------+----------------------------------------------------------------------------------------------
| Table    | Create Table
+----------+----------------------------------------------------------------------------------------------
| TestIDX2 | CREATE TABLE `testidx2` (
  `SomeValue` varchar(200) NOT NULL,
  KEY `SomeValue` (`SomeValue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+----------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Suggested fix:
Implement it, or show it in the creation DDL.

If prefix length is the same as full column length, why do you expect index on prefix? What is the point?

I've mentioned that I tried many lengths, and it's not 200 in the real code. It seems always ignored or, perhaps, is simply just never displayed.

I consider both to be bugs.

Dustin

I'll probably check with 5.5.12 on Windows tomorrow, but on current code I do not see any problem:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE `TestIDX2` (`SomeValue` VARCHAR(200) NOT NULL,
    -> INDEX(`SomeValue`(200))) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> show create table `TestIDX2`;
+----------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                          |
+----------+---------------------------------------------------------------------------------------------------------------------------------------+
| TestIDX2 | CREATE TABLE `TestIDX2` (
  `SomeValue` varchar(200) NOT NULL,
  KEY `SomeValue` (`SomeValue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table `TestIDX2`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `TestIDX2` (`SomeValue` VARCHAR(200) NOT NULL, INDEX(`SomeValue`(20))) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table `TestIDX2`;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                              |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
| TestIDX2 | CREATE TABLE `TestIDX2` (
  `SomeValue` varchar(200) NOT NULL,
  KEY `SomeValue` (`SomeValue`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

That nuance was it. In the platform I had a length of five hundred and I wanted to index the entire length. However, since the index length doesn't show with the default index length, nor does it show when prefix length equals the column size, it didn't seem as if it was working.

It was just coincidence that I happen to choose a prefix length that matched the column size in my test table, too.

Do you see my confusion, though? Is there any way to show the prefix length whenever it's been explicitly given? Otherwise, it just doesn't show at it's default value OR its maximum value, and that's very misleading for morons like me.

Dustin

I understand your confusion, but MySQL server here worked exactly as designed.

So, we can either make a documentation request from this report (I had not found any place in the manual explaining the fact that prefix setting equal to full column length is just ignored), or we can ask for a warning in some strict SQL mode for cases like that. What do you prefer?

I'm sorry for not responding to this. I thought I had.

I would prefer that it shows you. Say, for instance, you have a table with a certain column having a prefix length matching its column width. Then:

1) The SHOW CREATE TABLE command is useless for adding the prefix-length to the DDL for the CREATE statement, since it won't show the prefix unless it's non-default and it's less than the maximum.

2) If you require a prefix length that matches the column width, the only way to be sure that it's set correctly is to DROP the table and re-create. This means that even if it has thirty-million rows, a dump/backup will have to be performed, a re-creation, and a three-day reload. It seems insensitive to just leave it designed like this.

I'm pleading with you. Please get it into the SHOW CREATE TABLE, somehow.

Dustin Oprea

So, let's say that this is a feature request for SHOW CREATE TABLE to show prefix length when index was created on prefix, even if prefix length is equal to column size.