0

If I set a multi-column index -unique for example- with columns (A, B) and search by A or B independently, will they be as fast as if I also have simple indexes in A and B?

Are those extra simple indexes necessary?

Daniel Williams
  • 8,673
  • 4
  • 36
  • 47
axelbrz
  • 783
  • 1
  • 7
  • 16
  • possible duplicate of [Understanding multiple column indexes in mysql query](http://stackoverflow.com/questions/12728832/understanding-multiple-column-indexes-in-mysql-query) – Marco Mar 05 '14 at 23:50
  • MySQL actually does a pretty good job explaining how composite indexes work: http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html. – Gordon Linoff Mar 06 '14 at 00:05

2 Answers2

2

From MYSQL:MySQl 5 Reference

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

Mukus
  • 4,870
  • 2
  • 43
  • 56
1

If you create any index that is (A,B), MySQL can utilize that index for queries and sorts that have just A, or A then B. It can not use it for "B". The basic idea is that any prefix of the index is useful.

You don't have to create a separate one for "A", but you would need one for "B" if B was going to be sorted on or used in a where clause without "A".

Daniel
  • 4,481
  • 14
  • 34
  • I will choose the other answer as it's a little bit more complete, but thanks for your reply! ;) – axelbrz Mar 06 '14 at 00:34