Hands on: Quicker database queries

Which fields should you index?

Primary key fields are the most important fields to index, as I’ve said, but applying such indexes never appears on your to-do list because the database engine will do it for you. With that taken care of automatically, it is up to you to determine where other indexes should be applied in the remaining fields in your tables.

A simple and useful rule of thumb is to apply an index to any field you commonly use for querying. If you have a table holding Order data and you frequently run a query in order to see a list of the orders placed by customers on a particular date, you’d index the Date field.

If you also store a table of Customer data, like the lower one shown in screen 5, and you frequently search for customers by last name, you’d index the LastName field in that table.

So far, so straightforward, but let’s expand that second query slightly: now you want to search for a customer by last name and find the items they have bought. You design the query and run it. [A sample of the output is shown for customer Jones. What does the database engine do to find your answer?

The query looks for the name Jones in the LastName field. It then reads across the row to find her CustomerID, which is 1. Now it looks in the Order table at the foreign key field, also called CustomerID, in order to find any occurrence of 1.

This foreign key column is not indexed, so the engine is looking at a column in which the values are completely unordered. It has no choice but to look at every row in the table in order to be sure that it finds all the orders placed by Jones.

In this tiny sample table this isn’t a problem but, if the table had 10 million rows, the database engine would have to look at all of them. That will be very slow.

Hopefully, it is now clear that indexing the CustomerID foreign key field in the Order table would improve the performance of this particular query enormously.

So, can we assume all queries that draw information from two joined tables will be speeded up by indexing the foreign key field? Of course not, that would be too simple. In the example above, the query found a name in the Customer table and looked up corresponding stuff in the Order table.

But suppose we want to look for orders on a particular date and find out who placed them.

In that case our query would start in the Order table by finding the specified date (15 April, say) in the Date column.

It would then read across to the CustomerID foreign key field, and find 1 and 4.

Then it would inspect the CustomerID primary key field (which is automatically indexed) in the Customer table and read across for the corresponding customer names – Stephanie Jones and Janet Harris – to produce the answer that these two customers placed orders on 15 April.