In MySQL, how to build index to speed up this query?

Question

In MySQL, how to build index to speed up this query?

SELECT c1, c2 FROM t WHERE c3='foobar';

Noonan

What indexes have you got on t at the moment? – Woodsy

That's all in the manual: dev.mysql.com/doc/refman/5.5/en/create-index.html – Mosenthal

@Woodsy How do i check what indices exists? – Noonan

Answer 1

To really give a answer it would be useful to see if you have existing indexes already, but...

All this is assuming table 't' exists and you need to add an index and you only currently have a single index on your primary key or no indexes at all.

A covering index for the query will give best performance for your needs, but with any index you sacrifice some insertion speed. How much that sacrifice matters depends on your application's profile. If you read mostly from the table it won't matter much. If you only have a few indexes, even a moderate write load won't matter. Limited storage space for your tables may also come into play... You need to make the final evaluation of the tradeoff and if it is noticable. The good thing is it's fairly a constant hit. Typically, adding an index doesn't slow your inserts exponentially, just linearly.

Regardless, here are your options for best select performance:

1. If c3 is your primary key for table t, you can't do anything better in the query to make it faster with an index.

2. Assuming c1 is your primary key t:

   ALTER TABLE t ADD INDEX covering_index (c3,c2);  
   

3. If c1 is not your pk (and neither is c2), use this:

   ALTER TABLE t ADD INDEX covering_index (c3,c2,c1);  
   

4. If c2 is your PK use this:

   ALTER TABLE t ADD INDEX covering_index (c3,c1);  
   

5. If space on disk or insert speed is an issue, you may choose to do a point index. You'll sacrifice some performance, but if you're insert heavy it might the right option:

   ALTER TABLE t ADD INDEX a_point_index (c3);  
   

Evetta

Usually the order of your indexes should be WHERE columns first, ORDER columns last. There's no reason to index data columns that aren't involved in the filtering or ordering criteria. Creating tons of multi-level indexes only makes your INSERT operations cripplingly slow as you add more data. – Judijudicable

@Judijudicable to avoid point lookups you use covering indexes to improve performance. Covering indexes are ones containing all items in your select (as well as the the item in your where which you want to optimize search on). Read: mysqlperformanceblog.com/2006/11/23/… – Evetta

@Evetta A slight variant to my question: t has (c1, c2) as composite key, and the more important SELECT query i do is SELECT c1, c2 FROM t WHERE c2='Foobar';' Given that, would it mean that i basically already have the best performance possible -- (c1, c2)` are already index because of PK? – Noonan

@user1664196 nope. If your composit pk was (c2, c1) for that query you'd have the optimal index. Doesn't seem that different, but it is. – Evetta

does it make a difference whether you create indexes in bulk ie. (c3,c2,c1) or one by one ? – Kilauea

(c3,c2,c1) is a single composite (multi-column) index, not three separate indexes being created in bulk. See this: percona.com/blog/2014/01/03/… – Evetta

Answer 2

Build indexes on columns that you search for, so in this case, you'll have to add an index on field c3:

CREATE INDEX c3_index ON t(c3)

Tempe

With mysql CREATE INDEX is an alias mapped to ALTER TABLE <table> ADD INDEX either will work fine. – Evetta