Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it OK to name a MySQL index the same as the column it indexes?

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.

like image 955
sqlman Avatar asked Jun 27 '11 18:06

sqlman


People also ask

Does index Name matter MySQL?

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

Can indexes have the same name?

Because index names must be unique within the table, you cannot create or rename an index to have the same name as an existing PRIMARY KEY or UNIQUE constraint on the table.

How do I name an index in MySQL?

| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... When you add an index without specifying a name MySQL will generate a name based on the name of the first field in the index. If multiple indexes start with the same field the index name will be suffixed by a sequential number.

Can we use same index on two columns?

Introduction to MySQL composite indexA composite index is an index on multiple columns. MySQL allows you to create a composite index that consists of up to 16 columns. A composite index is also known as a multiple-column index.


1 Answers

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

like image 85
OMG Ponies Avatar answered Sep 22 '22 14:09

OMG Ponies