CODEWITHSUNDEEP
mysqloptimization
Amy Anuszewski
Amy Anuszewski

Reputation: 1853

Should I be using multiple single-column indexes or a single multi-column index?

This is a pretty basic question, but I'm confused by what I'm reading in various places. I have a simple table that doesn't contain a huge amount of data (less than 500 rows for any given db is typical) A typical query against this table looks like :

select system_fields.name from system_fields where system_fields.form_id=? and system_fields.field_id=?

My question is, should I have a separate index for form_id and one for field_id, or should I be creating an index on a combination of those two fields? I've never really done anything with multi-column indexes before.

 CREATE TABLE IF NOT EXISTS `system_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `reference_field_id` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `field_id` (`field_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=293 ;

Upvotes: 0

Views: 23

Answers (1)

Michael Y.
Michael Y.

Reputation: 661

If you are always going to query by these two fields, then add a multi-column index.

I'll also point out that if you're going to have < 500 rows in the table, your index may not even get used. Any performance difference with or without an index on a 500-row table will be negligible.

Here's a bit more (good) reading: https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/

Upvotes: 1

Related Questions