In MySQL, how to create an index to speed up this query?

Question

In MySQL, how to create an index to speed up this query?

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

Answer 1

To really give an answer, it would be useful to know if you have existing indexes, but ...

All this assumes that there is a table 't' and you need to add an index, and currently you only have one index for your primary key or no indexes at all.

The coverage index for the query will give the best performance for your needs, but with any index you sacrifice some insertion speed. How important this sacrifice is depends on your application profile. If you read mainly from the table, it does not really matter. If you have only a few indexes, even a moderate write load will not matter. Limited storage space for your tables may also come into play ... You need to make a final assessment of the trade-off, and if that is noteworthy. The good thing is a pretty constant hit. As a rule, adding an index does not slow down your inserts exponentially, just linearly.

Regardless, here are your options for the best performance choice:

• If c3 is your primary key for table t, you cannot do anything better in a query to make it faster with an index.

• Assuming c1 is your primary key t:

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

• If c1 is not your pk (and is not c2), use this:

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

• If c2 is your PC, use this:

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

• If disk space or insertion speed is a problem, you can choose a point index. You will sacrifice some performance, but if you insert heavy, this may have the correct option:

   ALTER TABLE t ADD INDEX a_point_index (c3); 

Answer 2

Create indexes for the columns you are looking for, so in this case you need to add an index to the c3 field:

 CREATE INDEX c3_index ON t(c3)