CODEWITHSUNDEEP
mysqlperformanceindexingmultiple-columns
hackartist
hackartist

Reputation: 5264

When is one index better than two in MYSQL

I have read about when you are making a multicolumn index that the order matters and that typically you want the columns which will appear in the WHERE clauses first before others that would be in ORDER BY etc. However, won't you also get a speed up if you just index each one separately? (apparently not as my own experiments show me that the combined index behavior can be much faster than simply having each one separately indexed). When should you use a multicolumn index and what types of queries does it give a boost to?

Upvotes: 0

Views: 104

Answers (3)

MarkR
MarkR

Reputation: 63616

A compound index (c1,c2) is very useful in the following cases:

  1. The obvious case where c1=v1 AND c2=v2
  2. If you do WHERE c1=v1 AND c2 BETWEEN v2 and v3 - this does a "range scan" on the composite index
  3. SELECT ... FROM t WHERE c1=v1 ORDER BY c2 - in this case it can use the index to sort the results.
  4. In some cases, as a "covering index", e.g. "SELECT c1,c2 FROM t WHERE c1=4" can ignore the table contents, and just fetch a (range) from the index.

Upvotes: 1

ziesemer
ziesemer

Reputation: 28707

A multi-column index will be the most effective for situations where all criteria are part of the multi-column index - even more so than having multiple single indexes.

Also, keep in mind, if you have a multi-column index, but don't utilize the first column indexed by the multi-column index (or don't otherwise start from the beginning and stay adjacent to previously-used indexes), the multi-column index won't have any benefit. (For example, if I have an index over columns [B, C, D], but have a WHERE that only uses [C, D], this multi-column index will have no benefit.)

Reference: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html :

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Upvotes: 3

O. Jones
O. Jones

Reputation: 108841

The short answer is, "it depends."

The long answer is this: if you sometimes do

 WHERE COL1 = value1 and COL2 = value2

and sometimes do 

 WHERE COL1 = value1

but never, or almost never, do

 WHERE COL2 = value2

then a compound index on (COL1, COL2) will be your best choice. (True for mySQL as well as other makes and models of DBMS.)

More indexes slow down INSERT and UPDATE operations, so they aren't free.

Upvotes: 2

Related Questions