Increasingly I find a very binary split between the professionals I come into contact with. One group of people are very database-aware and take the design of their storage quite seriously – with good results. The other group are more concerned with the functionality of their application, and have little regard for how it is stored other than considering it a keeping-place and making useful table and column names.

Too often though, they don’t think about how that data will be retrieved or what the implications are when it gets beyond the thousand records that were used for testing. This is where having an idea of how the data will be retrieved can really help application performance. (note: this article is aimed at users of traditional relational databases, and ignores all other possibilities). This post takes a look at the various index types and when to use them.For the technical detail, the best resource is the mysql manual itself (or of course the equivalent for your RDBMS of choice), showing how to add the various types of keys.

Primary Keys

Every table needs a primary key – this a unique key and will often be an auto_increment column, where every new record gets a new number incrementally. However a primary key can be any other unique piece of data in the table, for example a product SKU or a book ISBN.

Indexes

A general rule of thumb is to add indexes on columns which are used in a where clause – in layman’s terms, adding the index means mysql keeps track of the data in that column and can select using it as a criteria much more easily. If, for example, you always select a list of employees by their department, you’d add an index on the department column.

Composite Indexes

If you always select by two columns then you can add a composite index across both columns, so that mysql can use that index when it runs a select searching for both criteria. Be aware though that although the index can be used for a select statement involving one of the columns, this only applies to the first one – if you want to select by just the second one then you need to add an additional index on just one column.

Which Index Does MySQL Choose?

MySQL will try to guess which is the best index to use – in the next post in this series we’ll look at EXPLAIN to work out what indexes are being used on a given query – this can help us identify which indexes are useful to add.

edit: it took a really long time but I finally did write that entry about the EXPLAIN command