CODEWITHSUNDEEP
mysqlindexingdatabase-indexes
Drahcir
Drahcir

Reputation: 11982

Should I add multiple indexes?

If I have a unique index on a table that covers 2 fields, should I add another index on each field?

Example:

My table looks like this:

CREATE TABLE IF NOT EXISTS `my_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `usersID` int(11) NOT NULL,
  `userTypesID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I then add a unique index which covers both usersID and userTypesID:

ALTER TABLE  `my_table` ADD UNIQUE  `usersID_userTypesID` (  `usersID` ,  `userTypesID` )

Is it worth me adding 2 more indexes, one on usersID and another on userTypesID? e.g:

ALTER TABLE  `my_table` ADD INDEX (  `usersID` )
ALTER TABLE  `my_table` ADD INDEX (  `userTypesID` )

Would adding these extra indexes speed up some queries? Such as:

SELECT      `usersID`
FROM        `my_table`
WHERE       `userTypesID` = 101

Or

SELECT      `usersTypesID`
FROM        `my_table`
WHERE       `usersID` = 29

Upvotes: 2

Views: 3746

Answers (2)

olegkhuss
olegkhuss

Reputation: 3842

You don't need additional indexes, see here. Also try SHOW INDEXES FROM my_table

Upvotes: 1

Alnitak
Alnitak

Reputation: 340055

In theory the index on (usersID, userTypesID) will also act as an index on usersID by itself, because it's the left most column.

You would benefit from an index on userTypesID too.

Upvotes: 2

Related Questions