I have the following table structure
CREATE TABLE `table` (
`id` int(11) NOT NULL auto_increment,
`date_expired` datetime NOT NULL,
`user_id` int(11) NOT NULL,
`foreign_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date_expired` (`date_expired`,`user_id`,`foreign_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
As you'll notice, I have duplicate indexes on user_id: date_expired
& user_id
. I of course want the unique index because I want to ensure the data is unique.
The reason for the duplicate indexes is because without the user_id
index, my main search query takes 4 seconds. With the extra index it takes 1 second. The query is joining the table on user_id
and checking date_expired
.
The table only has 275 records.
- How bad is it to have a unique and normal index on the same field?
- How bad is it to have larger indexes than data when the table is purely ids?