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.

Answer 1

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.