CODEWITHSUNDEEP
mysqlperformanceindexing
Miles
Miles

Reputation: 5726

MySQL Secondary Indexes

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?

Upvotes: 2

Views: 1410

Answers (3)

vearutop
vearutop

Reputation: 4072

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/

Upvotes: 0

Puggan Se
Puggan Se

Reputation: 5846

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.

Upvotes: 2

LSerni
LSerni

Reputation: 57418

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.

Upvotes: 0

Related Questions