0

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.

David542
  • 104,438
  • 178
  • 489
  • 842

1 Answers1

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)
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks for the explanation, could you please explain what a `Covering Index` is and how that's useful? – David542 Mar 11 '16 at 22:22
  • 1
    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. – Reto Mar 11 '16 at 22:23
  • 1
    @David542, see this SO thread http://stackoverflow.com/questions/8213235/mysql-covering-vs-composite-vs-column-index as well as MySQL docs http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html – Rahul Mar 11 '16 at 22:26
  • 2
    This search `year + director + runtime` wont use your index – Mihai Mar 11 '16 at 22:31
  • @Mihai why wouldn't it? – David542 Mar 11 '16 at 22:34
  • @David542 The first column in the index needs to be the same with the LEFT column in the search.http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html If the search conditions are with AND ,the index will be used if the index starts with any columns in the search(not the case here) – Mihai Mar 11 '16 at 22:39