Bug #16212 create index can't use part of the column
Submitted: 5 Jan 2006 5:39 Modified: 6 Jan 2006 11:49
Reporter: Lu Tao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.16 OS:Microsoft Windows (windows/HP-UX)
Assigned to: CPU Architecture:Any

[5 Jan 2006 5:39] Lu Tao
Description:
create index can't use part of the column,while the manual says it can.

13.1.4. CREATE INDEX Syntax
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)

index_col_name:
    col_name [(length)] [ASC | DESC]

In MySQL 5.0, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.2, “ALTER TABLE Syntax”. 

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.5, “CREATE TABLE Syntax”. CREATE INDEX allows you to add indexes to existing tables. 

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns. 

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value. BLOB and TEXT columns also can be indexed, but a prefix length must be given. 

The statement shown here creates an index using the first 10 characters of the name column: 

CREATE INDEX part_of_name ON customer (name(10));

How to repeat:
$ cd /usr/local/mysql/bin
$ ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.16-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql>  create table a1(uuid varchar(32),code char(2));
Query OK, 0 rows affected (0.09 sec)

mysql> create unique index ui  on a1(uuid(10),code(1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid(10),code(1))' at line 1
mysql> create unique index ui  on a1(uuid,code);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create unique index ui2 on a1(uuid(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid(10))' at line 1

Suggested fix:
create table a1(uuid varchar(32),code char(2)); should work
[5 Jan 2006 7:55] Lu Tao
Suggested fix:
create table a1(uuid varchar(32),code char(2)); should work
is a bug too.
it should be
create index ui2 on a1(uuid(10))  should work
[5 Jan 2006 7:58] Lu Tao
It seems work when the column type is char but not work when the column type is varchar 
mysql> create unique index ui2 on a1(code(1));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
[5 Jan 2006 8:06] Lu Tao
oracle 9i supports create index ui3 on a1(substr(uuid,1,10));
while 
in MySQL
create index ui3 on a1(substring(uuid,1,10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'substring(uuid,1,10))' at line 1
[6 Jan 2006 11:49] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It's not about "part of the column" in the index, see where you got the syntax error, near 'uuid(10)' not near '(10)'. The problem is in the word 'uuid'.

UUID() is a  built-in MySQL function. So you need to write a space beween "UUID" and "(" if you don't mean a function in this context. See

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html