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 suffix to it, for example:

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:

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

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.

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Peter A. Shevtsov

The plural form of the word “index” is “indices”

0
Peter Zaitsev

Peter,

May be if you get too conservative this is the case, Though you can find a lot of “indexes” used as plural for index.
Some people say this is also preferred form these days:
http://wiki.answers.com/Q/What_is_the_plural_of_index

0
Monty Taylor

Seems like there are a couple of bug/feature requests in this post too. (to me, at least)

– MySQL allows duplicate identical index creation

(I can see no reason why two indexes on the exact same column would be useful – but maybe I’m wrong)

– MySQL creates crap auto_generated names

Instead of just using the first column name as the index name, how about firstcolumn_secondcolumn_thirdcolumn – etc – until it runs out of space? This is a common practice in the field anyway. (As is prefixing idx_ – although I don’t like this very much myself)

Anybody want to file there?

0
Peter Zaitsev

Monty,

I surely agree. Though I would make sure the index type is same as well (it is OK to have column indexes as normal BTREE index and FullText for example)
Regarding but reports – you should be in the best position to post them as MySQL/Sun employee.

I think I proposed fixing first one but it did not ended up going any where.

0
Olexandr Melnyk

> it will add numeric prefix to it

It’s a postfix or a suffix, but not a prefix 😉

0
Eric

> 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:

No, it won’t. It stops at 64.
(Don’t ask me how I know…)

– Eric

0
Olexandr Melnyk

> No, it won’t. It stops at 64.
> (Don’t ask me how I know…)

This applies not just to duplicate indexes, but is a generic MyISAM limitation.

0
Peter Zaitsev

Olexandr,

Thanks I changed prefix to suffix.

0
Gregory Haase

I was really surprised to discover that mySQL does not support naming the primary key. If you select contraint_name from information_schema.table_constraints, you get an aweful lot of constraints named “PRIMARY”. Do we really need this here, we have another column in the same table called constraint_type which happily tells us it’s a PRIMARY KEY.

But what surprised me the most was the fact that the following syntax does not produce an error:

CREATE TABLE test.testing
( testing_id INT NOT NULL auto_increment,
PRIMARY KEY pk_testing (testing_id)
);

You table will be happily created with primary key, but when you do a show create table, you get the following:

CREATE TABLE testing (
testing_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (testing_id)
)

As far as naming conventions, I usually name my foreign key constraints fk__ and my indexes i__# (and up until about 10 minutes ago I was naming my primary key constraints pk_).

0
krteQ

I just add, that this problem doubles while using FOREIGN KEYS. You just drop a foreign key and think the DELETE will perform how you expect, but…

0