Indexes, Analysts and SQL Smells

SQL Indexes - A Goldilocks problem for analysts or an SQL Smell
SQL Indexes – A Goldilocks problem for analysts or an SQL Smell

Database Indexes are something which a lot of analysts ignore as being “too technical”. This is a pity.

Several SQL Smells in Phil Factor’s article point at possible bad decisions. Thinking in the  Logical Model can improve these decisions.

Choosing the correct indexes is a typical “Goldilocks Problem”: not too few, not too many, just the right number! Bad or inadequate requirements will contribute to designers making bad decisions. Phil Factor describes having the wrong indexes as an SQL Smell!

What is an Index and what does it do for us?

If you are an analyst, you may not know exactly what an index is. In non-technical terms, an index provides a quick way for the database manager to find the rows it needs in a table. There are several sorts of index.

An index is a “thing” in its own right. An index takes up space in the database. Updating an index costs effort. The main benefit of an index is that it makes select or read operations faster.

Types: Unique, Non-Unique, Clustered

There are three main kinds of index: Unique, Non-Unique and Clustered.

Imagine that we have a very simply database consisting of 3 tables:

  • Customer (not shown in the diagram)
  • Order
  • OrderLine
Two tables: Order and OrderLine - Where to put the indexes?
Two tables: Order and OrderLine – Where to put the indexes?

Unique Indexes:

Candidate Unique Indexes on an "Order" SQL table
Candidate Unique Indexes on an “Order” SQL table

In the Order table we can see three columns which might be used to identify the order

If you are not familiar with GUIDs, they are a way of assigning identifiers or “keys”. They are worth finding out about. It would be unusual to expect a human being to type in a GUID. An “OrderNum” (which paradoxically might contain letters!) would be more convenient for the users.

We expect all three: OrderId, GUID and OrderNum, to be unique. Therefore, all three are candidates for Unique Indexes. If an application attempts to create a duplicate value in a column which has a unique index, then the database manager will raise an error and reject the transaction.

Non-Unique Indexes for Foreign Keys

Candidate Indexes on an "OrderLine" SQL table
Candidate Indexes on an “OrderLine” SQL table

In the OrderLine table you can see two columns which identify things in other tables: OrderId and ProductId. These are Foreign Keys. In this case we cannot say they are unique, but they are candidates for non-unique indexes.

We could also have used OrderNum or OrderGUID as Foreign Keys into Order.

It is good practice for the rows in the OrderLine table to have a unique identifier. There are two common ways of doing this.

  • We can assign an OrderLineId (which is unique across the whole table) or
  • we can use the combination of OrderId and OrderLineNum which together would identify a row.

In this example, both OrderLineId and the OrderId and OrderLineNum combination are (seperate) candidate unique indexes.

Clustered Indexes

The order of the rows in an SQL table is specified by the Clustered Index. Each table can have only one clustered index. The clustered index must be unique.

People often make the “primary key” the clustered index, but it is worth considering other options. In the example, OrderLines can be added to an order after it has been created.

Using the OrderId, OrderLineNum index as the clustered index would make the database store all the “lines” for one order together (whenever they were added to the order). That may be more efficient for retrieval. Phil Factor identifies two smells with the choice of clustered indexes.

Non-Unique Indexes for Searching

Candidate Indexes on a "Customer" SQL table - A Non-Unique index on Name would help searches
Candidate Indexes on a “Customer” SQL table – A Non-Unique index on Name would help searches

Columns which will be used for searching should be considered candidates for a non-unique index.

The role of Analysts in choosing Indexes and Index types

Indexes are usually specified in the “Physical Model”. The analyst can help the database designer make the right decisions, by applying a little thought. The analyst should not try to pre-empt the designers decisions. They should aim to assist by identifying relevant “candidates”.



Indexes enforce business rules like uniqueness in an SQL database. They influence database performance. Considering candidate indexes in the Logical Model and even the Conceptual Model will help database designers make better decisions.

Where next?

The next article is about the smell of nothing, or “Nulls”. Nulls present problems for developers and database designers.

Geek at speed! A ride in a Radical SR8 racing car.

I recently spent a few days in England on business. On my way home, I visited friends and business contacts I don’t see often enough. I thought I would share the achievements of a friend who doesn’t get the recognition he deserves.

This is a Radical SR8. It’s a track-day car or a road-going racing car, and is made in Peterborough. You you won’t see many on the road because they have been successful racing. (Top speed 178 mph)

Side view of Radical SR08
Side view of Radical SR08

My friend Steve Prentice designed the engine. The engine is two Suzuki Hayabusa cylinder blocks sharing a common crankshaft. The SR8 is a 2.6 litre V8 sports motorcycle engine in a lightweight chassis with an aerodynamic body! Steve describes the engine as his “best work so far”. The SR8 has held the road-legal lap record at the Nurburgring since 2009. (lap time 6m 48s which, if my calculations are correct, is and average of 112.9 mph!)

I normally avoid showing number-plates and other identifiable things on the internet but I’m making an exception in this case. This particular car is the 2009 specification and was used by Radical as a demonstrator. Steve bought it from Radical a few years ago. He has made a few modifications to the car to make it a little more practical on the road. He uses the car for advertising and for “testing things”.

View of the engine bay of Radical SR8
View of the engine bay of Radical SR8
Front view of SR08RAD Radical SR8 road-going track car.
Front view of SR08RAD Radical SR8 road-going track car.

A visit to Steve’s office, lair of the SR8

Steve lives with his wife near Banbury. His office is rented space in a barn in the middle of a field a short distance from his home. When I arrived at the office Steve was completing an overhaul of the SR8. That’s Steve with the bald head and the overalls!

Steve says the performance of the SR8 owes as much to the chassis and the brakes as to the engine. As you can see the chassis is a space-frame. The brakes are just something else!

I think Steve took pity on me or was fed up with me hanging around. He employed me to fetch and carry tools and to hold spanners. I even provided unskilled help filling it up with oil. With an SR8 even filling it with oil and water is not straightforward. Both lubrication and cooling systems have several potential airlocks. The filling process involves: filling, starting the engine, bleeding, stopping, checking the level and then repeating…

A sedate spin in the countryside

My reward (or punishment?) for being an unskilled labourer cum spectator, was a 40 minute test spin in Steve’s SR8. I needed instructions on how to get into the passenger seat. I also needed help with the 5 point harness.

The SR8 has no windscreen. Before we set off, the pair of us dressed up in black balaclava ski masks and perspex safety specs. Steve said that “we looked like a pair of bank robbers making a get-away”!

The performance of the engine, handling and brakes is everything you would expect. Steve was mostly driving on single carriageway roads. I couldn’t see the instruments, so I have to assume that the maximum speed we reached was 59.5 mph. The acceleration is literally breath-taking. The engine delivers not so much a “kick up the arse” as an “aggressive shove in the small of the back”, and the brakes are equal to it. The handling is excellent too. We hadn’t fitted the floor-pan under the engine bay so that any minor oil leaks would become obvious. As a result Steve said that he could feel a certain lack of down-force. I definitely didn’t notice anything!

I told Steve to pose like a proud father, so here he is. He goes to track days and race meetings, so if you see him with the car, introduce yourself. If you tell him you know me, he may give you a more detailed explanation of how the car is put together.

Steve Prentice, designer of the engine in the SR08
Steve Prentice, designer of the engine in the SR8