Should I add multiple indexes?

Question

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

score 2 · Accepted Answer · answered Oct 30 '12 at 16:09

2

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.

answered Oct 30 '12 at 16:09

Alnitak

334,560
70
407
495

score 1 · Answer 2 · edited May 23 '17 at 12:22

1

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

edited May 23 '17 at 12:22

Community

1
1

answered Oct 30 '12 at 16:19

olegkhuss

3,762
2
17
7

+1, Thanks for the reference question, it confirms Alnitaks answer to be correct, because as stated in the answer mysql can use the leftmost index. – Drahcir Oct 30 '12 at 16:28
@Gerve the link in my answer (possibly not very obvious) also talks about using the left-most column. I said "in theory" in my answer because I've seen circumstances when an index on just the one column worked much better than an index on two. – Alnitak Oct 30 '12 at 16:45
I'd suggest trying EXPLAIN SELECT .. WHERE usersID=1 EXPLAIN SELECT .. WHERE userTypesID=1 and vice versa, you will see when index is used and when not. And yes Alnitak's link expains how it works. – olegkhuss Oct 30 '12 at 16:48

Should I add multiple indexes?

2 Answers2