EmergencyEMERGENCY? Get 24/7 Help Now!

Should you name indexes while doing ALTER TABLE ?

 | May 28, 2008 |  Posted In: Insight for DBAs

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

10 Comments

  • 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

  • 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?

  • 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.

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

  • 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_).

  • 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…

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.