What are Indexes?
Every time your web application runs a database query containing a WHERE statement, the database server's job is to look through all the rows in your table to find those that match your request. As the table grows, an increasing number of rows need to be inspected each time.
Indexes solve this problem in exactly the same was as the index in a reference book, by taking data from a column in your table and storing it alphabetically in a separate location called an index. The same process can be applied to all data types, for example numeric data will be stored in numeric order and dates in date order.
By doing this, the database does not need to look through every row in your table, instead it can find the data you are searching for alphabetically, then skip immediately to look at the row(s) where the data is located.
Creating a Simple Index
Many web frameworks will assist with the creation of indexes, and it is worth researching how to create indexes using your framework, such as migrations in Ruby on Rails, but in this article I will provide the raw MySQL commands.
In this example, we will be working with a table of students at an extraordinarily large school. This is quite a large table as we have 100,000 students in our school. The first few rows of the table look like this:
ID First Name Last Name Class
1 James Bond 6A
2 Chris Smith 6A
3 Jane Eyre 6B
4 Dave Smith 6B
The table was created using the following query:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`class` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Our web application performs 3 queries against this table:
- Look up a student by ID
- Search for students by last name
- List all students in a class
Each of those queries searches for data from a single column, so we should ensure that each of these columns has its own index.
The first query SELECT * FROM students WHERE id = 1 is a special case because it is looking up a row using its PRIMARY KEY. Because we already know exactly which row we want, no further optimization is required. It is best to always look rows up in this way when possible, and almost all tables should have a unique column defined as the primary key in this way.
The second query SELECT * FROM students WHERE last_name = 'Smith' will be searching a column which does not currently have an index and this is exactly the type of query that will search the whole table unless we do something about it. Lets go right ahead and create an index on that column:
CREATE INDEX by_last_name ON students (`last_name`);
This creates an index named by_last_name on the table, which will contain an indexes copy the last_name column, allowing us to look these up much more quickly.
Exactly the same principle can be applied to the class column, allowing us to efficiently look up the students in a particular class. We should create a simple index on it as follows:
CREATE INDEX by_class ON students (`class`);