Over a million developers have joined DZone.

Indexes on Tables

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.


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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


Published at DZone with permission of Lorna Mitchell, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}