One of the more vexing problems with relational database development is designing appropriate indexes. Perhaps the most important thing you can do to assure optimal application performance when accessing data in a relational/SQL database is to create correct indexes for your tables based on the queries your applications use. Of course, this is easier said than done.
But we can start with some basics. For example, consider this SQL statement:
SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO = '000010' AND DEPTNO = 'D01';
What index or indexes would make sense for this simple query? First, think about all the possible indexes that you could create. Your first short list probably looks something like this:
This is a good start, and Index3 is probably the best of the lot. It enables the DBMS to use the index to immediately look up the row or rows that satisfy the two simple predicates in the WHERE clause. Of course, if you already have a lot of indexes on the EMP table, you might want to examine the impact of creating yet another index on the table.
With the caveat that appropriate index creation can be complicated, let’s look at the Top Ten things you can do to build the right indexes on your tables:
Following these Top Ten index design techniques can go a long way toward improving not just your index usage, but also the performance of your database applications.