Hands on: Quicker database queries

Indexes can make queries run, quite literally, thousands of times faster. However, in order to use indexes efficiently, you have to understand them.

First, the practical bit: how do you apply an index to a field/column? In Access you simply open up the table in design mode, select the field to be indexed and change the indexed property to ‘Yes’.

There are two ‘Yes’ options. One allows the field to have duplicate entries, the other doesn’t. The one you choose will depend on the type of data you have in the field. Foreign keys, for example, normally do have duplicate entries .

Of course, all database engines have different interfaces. In SQL Server, for example, you right-click on the table name and select All Tasks, Manage Indexes. That opens up a dialogue box from which you can select New.

Whatever database you use, applying an index is easy.

How does indexing work?

Indexing is a complex subject; there are entire books devoted to the subject, so anything I write here is a major simplification. But understanding even the basic principles is a significant help to anyone trying to speed up a database.

There are two basic flavours of index: clustered and non-clustered. A clustered index essentially sorts the records of the table on the disk itself.

For example, if we had a table like that shown in screen 3 and applied a clustered index to the OrderID field, then the rows would be moved around on disk until they were in the order shown.

The database can now find, for example, OrderID number 7 very easily. It knows that the rows are in ascending order so it doesn’t start at the top and look at every row. Instead it would jump about two-thirds of the way down the table and read a row.

If that has the value 6 then it knows to look lower down the table. In other words, the database does exactly what you or I would do and jumps around in the sorted list until it finds the value it needs. Given eight rows there is no real gain; given 20 million rows, the index makes a huge difference.

However, there is a slight problem. If we now apply a cluster index to the Quantity field, the records would be re-ordered on the disk as shown in screen 4.

We can now find all the orders for, say, two items very easily. But, since a clustered index moves the records around on the disk, we have lost the ability to find specific orders rapidly. It is clearly impossible to have more than one clustered index per table.

Cluster indexes allow data to be found very rapidly, so the database engine will normally apply a cluster index to the primary key field of any table completely automatically.

Non-clustered indexes are the other main type of index. They don’t move the records around on the disk. They are not as fast as clustered indexes, but the good news is we can have as many of them on a table as we like.