CODEWITHSUNDEEP
mysqlindexing
Jjreina
Jjreina

Reputation: 2713

MySQL Create table with two indexes on same column(s)

Is it possible to create two indexes with names different on the same column?

Upvotes: 2

Views: 4279

Answers (1)

StuartLC
StuartLC

Reputation: 107237

Yes, you can, but why would you do that?

Unless the indexes are different in some way, for example if there are additional columns, or differences in the order of the columns in the indexes, a second duplicated index would be redundant.

Each additional index on a table requires more disk storage (slight cost increase), and also means more data needs to be written when inserting, updating or deleting data (slightly slower writes).

But yes, it is possible, and the syntax is one would expect, e.g. given the table:

CREATE TABLE T1
(
  col1 INT,
  col2 INT
);

CREATE INDEX IX1 on T1(col1);
CREATE INDEX IX2 on T1(col1);

SQL Fiddle here

Upvotes: 4

Related Questions