Search code examples
mysqlindexingsql-optimization

Proper indexing for an InnoDB table

I have an InnoDB table with the following columns

  • id MEDIUMINT UNSIGNED AUTO_INCREMENT
  • a VARBINARY(16)
  • b CHAR(2)
  • c VARCHAR(100)
  • d MEDIUMINT UNSIGNED
  • e TINYINT UNSIGNED, range 0-49

I'll be performing such a query most of the time

SELECT d, e, c FROM tbl WHERE a=0x5caffbb2 AND d>1000000 AND (e=1 OR e=5 OR e=6 OR e=8 OR e=15)

How should I set up indexes for

  1. maximum SELECT speed
  2. decent SELECT speed and high INSERT speed
Solution

  • Achieve maximum select speed via composites or covered indexes. Problem is, your varchars will get in the way. They (at least one at the moment) are too wide for covered, but your ints are not. So consider choosing a composite index in the most frequently used fashion such as

    key (i1,i2,i2)

    for some ints.

    For maximum insert speed, choose LOAD DATA INFILE, the hands down fastest approach, even in cases of interactive GUI apps. It takes some configuration of worktables to bring in data. You will need to juggle concepts such as whether or not the data previously existed. And often craft special update with a join pattern solutions for getting the data into the real table from the work table. The fact of the matter is that no looping with binding will achieve this throughput.