You are here

Should you name indexes while doing ALTER TABLE ?

MySQL Performance Blog - Thu, 29/05/2008 - 3:54am

MySQL Server does not require you to specify name of the index if you're running ALTER TABLE statement - it is optional. Though what might be good practical reasons to specify the key name or omit ?

Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining the indexes.

Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will add numeric prefix to it, for example:

PLAIN TEXT SQL:
  1. mysql> CREATE TABLE t1(i int, j int);
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. mysql> ALTER TABLE t1 ADD KEY(i,j);
  5. Query OK, 0 rows affected (0.03 sec)
  6. Records: 0  Duplicates: 0  Warnings: 0
  7.  
  8. mysql> ALTER TABLE t1 ADD KEY(i);
  9. Query OK, 0 rows affected (0.00 sec)
  10. Records: 0  Duplicates: 0  Warnings: 0
  11.  
  12. mysql> SHOW CREATE TABLE t1 \G
  13. *************************** 1. row ***************************
  14.        TABLE: t1
  15. CREATE TABLE: CREATE TABLE `t1` (
  16.   `i` int(11) DEFAULT NULL,
  17.   `j` int(11) DEFAULT NULL,
  18.   KEY `i` (`i`,`j`),
  19.   KEY `i_2` (`i`)
  20. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  21. 1 row IN SET (0.00 sec)

Such automatically table generated names are not really helpful for multiple column indexes - you can't easily see what is the index MySQL trying to use from explain, such as if you have some smart index naming conversion such as idx_i_j for the first index it would be very explanatory.

Next comes the question of maintaining the indexes.
What happens if you try to create the same index without specifying index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning:

PLAIN TEXT SQL:
  1. mysql> ALTER TABLE t1 ADD KEY(i);
  2. Query OK, 0 rows affected (0.00 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> ALTER TABLE t1 ADD KEY(i);
  6. Query OK, 0 rows affected (0.00 sec)
  7. Records: 0  Duplicates: 0  Warnings: 0
  8.  
  9. mysql> ALTER TABLE t1 ADD KEY(i);
  10. Query OK, 0 rows affected (0.01 sec)
  11. Records: 0  Duplicates: 0  Warnings: 0
  12.  
  13. mysql> SHOW CREATE TABLE t1\G
  14. *************************** 1. row ***************************
  15.        TABLE: t1
  16. CREATE TABLE: CREATE TABLE `t1` (
  17.   `i` int(11) DEFAULT NULL,
  18.   `j` int(11) DEFAULT NULL,
  19.   KEY `i` (`i`,`j`),
  20.   KEY `i_2` (`i`),
  21.   KEY `i_3` (`i`),
  22.   KEY `i_4` (`i`),
  23.   KEY `i_5` (`i`)
  24. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  25. 1 row IN SET (0.00 sec)

However if you would specify index name MySQL will complain if you try to create index with same name again:

PLAIN TEXT SQL:
  1. mysql> ALTER TABLE t1 ADD KEY idx_i(i);
  2. Query OK, 0 rows affected (0.02 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> ALTER TABLE t1 ADD KEY idx_i(i);
  6. ERROR 1061 (42000): Duplicate KEY name 'idx_i'

Adding indexes without name specified is very common reason why systems tend to get duplicate indexes. BTW make sure you check yours with mk-duplicate-key-checker.

For example you may have run ALTER TABLE on the SLAVE server to check things out and when run it again on the master to apply changes to all SLAVEs... and so you get two copies of indexes on the slave without any notice.

The other side of the problems comes from the automatic naming - depending on order you add indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated.

If you use auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to the box - this is especially concern for cases when deployment is done by the end user outside of developing organization as in this case there is little control over what customization user may have done.

As a Summary - if is good to name your indexes manually both for documentation purposes and ease of maintenance. This may sound pretty obvious but few organizations using MySQL have consistent process of always naming indexes on production systems.

Entry posted by peter | 4 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks