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.
I believe if you created your unique index as (user_id
, date_expired
, foreign_id
), you'll get the same benefit of having a normal index on user_id
with just the unique index. MySQL can use the first columns of any index to pare down the number of rows in the join in the same manner as an index on user_id
.
See MySQL's index documentation for more information.
Are you referring to the id
auto_increment column elsewhere in your schema to save space? Since your unique index covers all of the other columns in your table, it is in essence a primary key itself and could be dropped if you're not.
You can check what keys your query is using by prefixing it with EXPLAIN.