CODEWITHSUNDEEP
mysqlsqlindexing
skiwi
skiwi

Reputation: 69359

Will indexes get used if you query a subset of the columns of the index?

So consider I have the following table:

table:

Assume I use queries that differ at runtime (a website for example where checkboxes denote whether or not to search on a specific field), some possible instances:

(Using some x, y, z for the values)

The questions:

  1. What is the effect of an index on (c1, c2, c3, c4, c5)? How will it be used in the several queries?
  2. What would the best indexes be to create on this table?

Upvotes: 4

Views: 2278

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

MySQL documentation does a good job of explaining how multi-column indexes work here.

In general, the index can be used for a where clause when some number of column on the leftmost side of the index have equality conditions. Your where clauses use the following columns:

  • c1, c2
  • c1, c2, c3, c4
  • c1, c2
  • c1, c2, c5

Any index starting with columns c1 and c2 would normally be used for these queries. MySQL can apply other conditions to using the index, such as selectivity. That is, if c1 and c2 have constant values (for instance) then using an index will not benefit the query.

To optimize all these combinations, you can create two indexes: c1, c2, c3, c4 and c2, c1, c5. The reason for swapping c1 and c2 in the second index is so you could handle queries where the condition is on c2 but not c1, as well as the reverse.

Upvotes: 7

halfbit
halfbit

Reputation: 3954

if you use ìndex on (c1,c2,c3) the database will create and index good for searching ... where c1=sth and c2=sth with or without c3.

This will perform better than seperate indices.

But is is somehow useless if you need only C2 as where clause

if you need c3 as only indext col, too, then create an additional index for it.

Upvotes: 2

Related Questions