The Power of Database Indexes - Help Writing Faster Queries - Part 1 of 3

Good evening! Well, it's evening here in Missouri at the time I am writing this. ;)

Before I go any further let me say we will be making some videos to show you how to do this stuff. So, if you are one of those people who learn better / faster by watching rather than reading check back soon. The video lesson for Part 1 will be available within a day or two at most. I actually plan on doing this first video tomorrow after work.

Okay, now are you ready to pick up some quick help writing faster sql queries? I hope so because we are now just moments away from Part 1 of this little series.

Let's go!


Help Writing Faster SQL Queries
Part 1 : The Power of Database Indexes

Many developers ignore indexes altogether. Many do not understand them.

An index is merely a Table of Contents to the data in the database table. While developing an application, print out the schema of the database, including indexes, so you know what they are while writing your queries.

If an appropriate index is missing, have someone experienced with indexes create it (whether that be you, the developer on the other side of the wall, or a DBA).

Here’s the impact. Say you are writing a contact management system. You would probably have a table with first name, last name, etc. For simplicity, we’ll keep this all one big table and obvious field names.

Want an example? Okay, you’d probably write a query that looks likething like:

Select Person_ID, Name_First, Name_Last
From Person_Data
Where Name_Last = @NameLast
And Name_First = @NameFirst

With 100 rows (ie. records), this query will work instantly. However, as your database grows to several thousand rows, this query will slow down, eventually hitting the seconds range. If you merely add an index with the columns Name_Last and Name_First, you will have basically instant return time for the life of the application.

I bet indexes don't seem so complicated now, do they?

Just remember to look for the columns (Name_Last and Name_First in our example above) that appear in the Where clause. These are the columns that need an index.


Creating the index

There are different ways to create the actual index. If you are more comfortable with GUIs then use Enterprise Manager or the appropriate tool for your database.

If you are comfortable working in the query window you can use the command format to build your index.

create index INDEX_NAME_HERE on TABLE_NAME_HERE (column_name, column_name, ... etc, and so on, and so forth).

This is how you create the index needed for the query above:

create index IX_PersonData_NameLastNameFirst on Person_Data (Name_Last, Name_First)

Hope you have enjoyed this first tip and now have a better understanding of indexes.

If you have any questions please post them on this blog!

Just use the link immediately below this post (the one that says comments).


Happy indexing!

The SQL Coach Team