What is the significance of the index name when cr

Question

I've done something like this in order to use on duplicate key update:

CREATE UNIQUE INDEX blah on mytable(my_col_to_make_an_index);

and its worked just fine. I'm just not sure what the purpose of the index name is -- in this case 'blah'. The stuff I've read says to use one but I can't fathom why. It doesn't seem to be used in queries, although I can see it if I export the schema.

So ... what purpose does the index name serve? If it helps the line in the CREATE TABLE ends up looking like:

UNIQUE KEY `clothID` (`clothID`)

Answer 1

The naming is to allow global namespace, and help better understand on the table schema.

The index name is very useful for forcing index hint. Try not using the same name for both index and column (ambiguous), and camel case is meaningless for system like Windows (which does not allow case sensitivity).

For example, like this:

unique key cloth_id_uniq (cloth_id); 
>>allow people knowing this an unique key on column cloth_id

fulltext key description_ft (description); 
>>allow people knowing this index is fulltext on column description

I do not think there is a standard naming convention, whatever seems intuitive will help most.

Answer 2

The index name is used to reference the index for future commands. Like drop index.

http://dev.mysql.com/doc/refman/5.0/en/drop-index.html

Just think of index names like table names. You can just as easily make a table called 'blah'.

CREATE TABLE blah (f1 int);

But 'blah' isn't very helpful for future reference. Just be consistent. something like

CREATE UNIQUE INDEX field_uniq on mytable(field);

or

CREATE INDEX field1_field2_inx on mytable(field1, field2);

Answer 3

You are correct. The index name is only used as an identifier, so you can refer to it if you need to edit, delete or query on it later. It isn't used for any other purpose.

You can name the index whatever is most convenient for you, but some consistency would probably help you (naming your index 'blah' is perfectly valid, but you won't have a clue where it is, what it does, or why you created it).