Preferable index structure in mysql table

Question

I have the following database structure:

• id
• name
• year
• director
• runtime

An example entry being: (1, "Titanic", 1994, "James Cameron", 02:44:00)

I want to find a match by any three of the four criteria, for example:

1. name + year + director
2. name + year + runtime
3. year + director + runtime

If I created one index, would that be sufficient for optimial queries?

ALTER TABLE myTable ADD INDEX (name, year, director, runtime)

Or, do I need to create an index for each combination?

ALTER TABLE myTable ADD INDEX (name, year, director)
ALTER TABLE myTable ADD INDEX (name, year, runtime)
ALTER TABLE myTable ADD INDEX (year, director, runtime)

I would obviously prefer to use the first, but which would be suggested here? Why would one be preferable to the other? This is an InnoDB table.

• Mihai: You could add a FULLTEXT index on all 4 columns and search with WHERE MATCH(all 4 columns) AGAINST (words) IN BOOLEAN MODE
• David542: @Mihai I'm using InnoDB.
• Mihai: On 5.6 or higher you can use FULLTEXT on innoDB

Answer 1

Considering that id is a primary key having default unique index; if you create one index with 4 columns that would be sufficient cause in that case what you are getting is a Covering Index.

ALTER TABLE myTable ADD INDEX (name, year, director, runtime)

• David542: thanks for the explanation, could you please explain what a Covering Index is and how that's useful?
• Reto: A covering index refers to the case when all fields selected in a query are covered by an index, in that case InnoDB (not MyISAM) will never read the data in the table, but only use the data in the index, significantly speeding up the select.
• Rahul: @David542, see this SO thread stackoverflow.com/questions/8213235/… as well as MySQL docs dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
• Mihai: This search year + director + runtime wont use your index
• David542: @Mihai why wouldn't it?