8

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

SELECT c1, c2 FROM t WHERE c3='foobar';
qazwsx
  • 25,536
  • 30
  • 72
  • 106

2 Answers2

12

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);
Ray
  • 40,256
  • 21
  • 101
  • 138
  • 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. – tadman Oct 24 '12 at 14:35
  • 1
    @tadman 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: http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/ – Ray Oct 24 '12 at 14:37
  • @Ray 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? – qazwsx Oct 25 '12 at 01:36
  • @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. – Ray Oct 25 '12 at 02:18
  • does it make a difference whether you create indexes in bulk ie. (c3,c2,c1) or one by one ? – Xsmael Mar 16 '20 at 17:58
  • `(c3,c2,c1)` is a single composite (multi-column) index, not three separate indexes being created in bulk. See this: https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/ – Ray Mar 16 '20 at 18:46
7

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)
Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
  • 4
    With mysql `CREATE INDEX` is an alias mapped to `ALTER TABLE ADD INDEX` either will work fine.
     – Ray Oct 24 '12 at 15:05