indexing - 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';

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);