mysql do compound indices index individual fields as well?

Question

I have the following MySQL/InnoDB table. I added a compound index as the primary key on both columns, and I have also added two single column indexes. With the compound index in place am I getting any performance increase from the single column indexes? or would I be better to drop them?

CREATE TABLE `keywords` (
  `keyword` varchar(100) NOT NULL,
  `record_id` int(11) NOT NULL,
  PRIMARY KEY (`keyword`,`record_id`),
  KEY `keyword` (`keyword`),
  KEY `record_id` (`record_id`),
  CONSTRAINT `keywords_record_id_records_id` FOREIGN KEY (`record_id`) REFERENCES `records` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

If it helps, the main purpose of this table is as a forward index, so this table is exclusively used with record_id INNER JOINed to the id column of my main record table.

score 3 · Accepted Answer · answered Jun 15 '12 at 06:10

The keyword column is not beneficial, but the recordid one definitely is (especially since you're using that heavily on inner joins). The first column is the main index, the second is only a "tiebreaker", and it's usually no substitute for having another index with that column first.

Think of it like this ... say you have a list of people's info, ordered first by name, then by phone number. If you need to look up a person's name, this list is more than fine; you don't need a second one ordered by name only. But if you need to look up someone by phone number, then this name-then-phone ordered list is just as useless as if there were no order at all; you would need another list ordered by phone number first.

mysql do compound indices index individual fields as well?

1 Answers1