Reputation: 11982
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