MySQL multiple indexes vs multi-column index for searching

Question

In the software I'm writing one has the ability to search a given table for information. The search form has 5 fields, all which of course correspond to different columns in a table, but all of the fields are optional.

My question is in regard to whether or not multi-column indexing will work and the proper way to build a query for it.

If I have a single index across 5 columns, and I build a query to search them, when it comes to fields in this index I'm not searching, do I do something like:

field1 = 10 AND field2 > 0 AND ...

Or should I not include the unused columns at all?

I've searched around for information about multiple column indices, but I can't seem to find what to do when you need to skip one of the columns in a given index or if you simply don't care about that one in that specific instance.

Do everything you can to avoid table scans. This is when a query has nothing to index off of. Never use table scans unless the tables rows are a fixed length, and is small. Like 1-20 rows. — BradLaney
– BradLaney, Commented Jul 20, 2011 at 3:16

bash- · Accepted Answer · 2011-07-20 04:07:16Z

9

You have to understand in MySQL (in the case of InnoDB) it only uses the left most prefix of your indexes. So if the index is in Field1 + Field2, querying with only Field2 in the WHERE cannot make use of the whole index.

And if Field1 + Field2 + Field3 is the index, a query with only Field1 & Field3 in WHERE the same would happen.

Option 1

You would have to create a separate index for each search scenario if you want to optimize each search. However, if the tables are large, then the indexes would become extremely large as well. If those search queries are made very often this would be worth it.

Option 2

You can use a nifty trick if your searches have a low selectivity (i.e. Gender) by putting the low selectivity columns to the left most and use Gender IN(M, F) to include it in the WHERE clause along with the other column(s) to make use of the whole index.

edited Jul 20, 2011 at 4:07

answered Jul 20, 2011 at 4:00

bash-

6,33410 gold badges45 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zerkms · Accepted Answer · 2011-07-20 03:07:43Z

3

For query with criteria

field1 = 10 AND field2 > 0

you need to have composite index field1 + field2 (in this particular order)

answered Jul 20, 2011 at 3:07

zerkms

256k73 gold badges447 silver badges551 bronze badges

3 Comments

KaKa Over a year ago

so index is not used if querying only field2. So if the queries can be anything, its better to have individual indexes.

zerkms Over a year ago

@kaka: nope. This index will be used if both fields take part in query, or only field1. For different queries there are some tricks, except of N single indexes

Dex Over a year ago

@kaka, that's correct because they are pre-ordered as field1, then field2. Indices in MySQL are read from left to right.

Dex · Accepted Answer · 2011-07-20 03:09:55Z

1

It depends on your queries. If you will be doing many queries with field1 and field2 often, then it makes sense to have a composite index. Otherwise you might get by fine on multiple single column indices.

answered Jul 20, 2011 at 3:09

Dex

12.8k15 gold badges71 silver badges94 bronze badges

Collectives™ on Stack Overflow

MySQL multiple indexes vs multi-column index for searching

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related