29

Is it considered bad form to give an index the same name as the column it is based on?

Like if you have a column named 'foo' and you want to create a normal index on it, would it be okay to name the index 'foo'? MySQL doesn't complain, but I am wondering what the pros and cons are.

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
sqlman
  • 631
  • 1
  • 7
  • 8
  • It's okay to do so because MySQL uses indexes differently than column names and so they do not conflict. But it's best to follow a naming convention so that you can easily distinguish between the two, perhaps add a prefix to your indexes like 'id_' or 'idx_' or 'ix_' – Abhay Jun 27 '11 at 18:27

3 Answers3

17

The convention doesn't matter so much as that you are consistently using it.

That said, I prefix index names with "ind_"; Constraints get the "cns_" prefix. In either case, the column name(s, if composite/covering).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
10

As a convention, index names should begin with the name of the table they depend on, like:-

Let the table name be "USERS", and the names of the fields be "id" & "username", for example. Then the index name could be "IDX_USERS_ID_USERNAME".

However, don't worry too much on the convention, as long as they are understandable by anybody who will access the database after some months, without your help.

Hope it helps.

Knowledge Craving
  • 7,955
  • 13
  • 49
  • 92
  • 3
    Some databases (Oracle) have a character limit; table name shouldn't be necessary unless the same column name is in more than one table... – OMG Ponies Jun 27 '11 at 18:36
  • Yes, I totally agree with your point. I actually wanted to provide a very general example, with consideration for the JOINing of tables also. But my mistake of not mentioning it in the answer. Thanks! – Knowledge Craving Jun 27 '11 at 18:41
2

Indexes are not columns and index names are only for readability. You can name them whatever you want. If the index is on a single column then it makes sense to give it the same name as the column.

Dan Simon
  • 12,891
  • 3
  • 49
  • 55