MySQL Secondary Indexes

Question

If I'm trying to increase the performance of a query that uses 4 different columns from a specific table, should I create 4 different indexes (one with each column individually) or should I create 1 index with all columns included?

You should try out each idea that you can come up with and compare performance before and after, and then use the one that gives the best overall performance increase across all use cases. — mellamokb, Jul 10 '12 at 19:22
I don't mind trying out each one... the main problem is creating these indexes will probably take 8-10 hours each and the faster this problem can be solved the better. If no one had any specific reason to do one or the other, this was going to be my approach — Miles, Jul 10 '12 at 19:23
Take a subset of your data, dump it into sqlfiddle, and at least you can look at the execution plans without taking 8-10 hours. — lc., Jul 10 '12 at 19:24
nice, thanks lc... didn't know about that one. I am not a MySql guy ;) — Miles, Jul 10 '12 at 19:26
http://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practises — Taz, Jul 10 '12 at 19:33

score 2 · Answer 1 · edited Jul 10 '12 at 19:32

2

One index with all 4 values is by my experience the fastest. If you use a where, try to put the columns in an order that makes it useful for the where.

edited Jul 10 '12 at 19:32

Justin Swartsel

3,451
1
20
24

answered Jul 10 '12 at 19:23

Puggan Se

5,738
2
22
48

score 0 · Answer 2 · answered Jul 10 '12 at 19:28

An index with all four columns; the columns used in the WHERE should go first, and those for which you do == compare should go first of all.

Sometimes, giving priority to integer columns gives better results; YMMV.

So for example,

 SELECT title, count(*) FROM table WHERE class = 'post' AND topic_id = 17
    AND date > @@BeginDate and date < @@EndDate;

would have an index on: topic_id, post, date, and title, in this order.

The "title" in the index is only used so that the DB may find the value of "title" for those records matching the query, without the extra access to the data table.

The more balanced the distribution of the records on the first fields, the best results you will have (in this example, say 10% of the rows have topic_id = 17, you would discard the other 90% without ever having to run a string comparison with 'post' -- not that string comparisons are particularly costly. Depending on the data, you might find it better to index date first and post later, or even use date first as a MySQL PARTITION.

Then again, how many queries are there? You should calculate the query cost multiplied number of accesses to that query; keeping in mind that index-maintenance adds to overall costs (e.g. INSERTs and UPDATEs will run a bit slower). — LSerni, Jul 10 '12 at 19:34

score 0 · Answer 3 · answered Jul 10 '12 at 22:34

Single index is usually more effective than index merge, so if you have condition like f1 = 1 AND f2 = 2 AND f3 = 3 AND f4 = 4 single index would right decision.

To achieve best performance enumerate index fields in descending order of cardinality (count of distinct values), this will help to reduce analyzed rows count.

Index of less than 4 fields can be more effective, as it requires less memory.

http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/

MySQL Secondary Indexes

3 Answers3