The Power of Indexes in Your Database Tables

Most anybody that has worked with a database understands that almost every table has a "primary key" that acts as an address at which to find a given record. It's a unique identifier of a specific record that makes it quick and easy to find it. It's called an "index".

What many newbies to databases don't understand, or at least fully appreciate, is that there is another type of index that can be used across multiple fields on a single table. These indexes are used on fields that are queried or compared in "where" clauses a lot. Many databases use "foreign keys" which is a combination of an index and a constraint. Being a MySQL guy, foreign keys are not something I use a lot, but indexes I do.

In index is essentailly a record of shortcuts to records with specific values. It's a database structure that allows quick lookup of in one column, or many columns of a database table. While I am by no means a SQL guru of any sort, my basic rule of thumb is that I index two types of columns, one, the primary key, which is typically indexed by default upon being declared a primary key. Secondly, I index columns that are frequently used in "where" clauses or "on" clauses in joins. A well designed database will typically have such columns as integer fields, though something character fields are used as well.

For example, say you have a table with news articles in it, categorized into their own categories that are listed in a different table. The best practice would be to index the "category_id" field in the articles table to make quicker work of the "WHERE category_id ='#'" query, which would undoubtly be very common. It is assured that article_id on the articles table and category_id on the categories table would be indexed as the primary key, so, indexing the category_id field in the articles table, the field that relates the two tables, should be indexed as well.

Integer fields query much faster than the character fields, so, whenever possible, do your queries against integer fields. They also create smaller and faster indexes. Syntax to add an index is usually something along the lines of:

CREATE INDEX index_name ON table_name (column_name)

Varying slightly from database type to database type. Check syntax for your specific database.

I have had this simple formula speed up database queries subtantially at times, I mean very substantially. Especially in the case of large queries joining on several tables that are very large. Smaller tables do not necessarily improve that much as queries are quick on small tables anyway.

Try it, if you have performance problems on your web site, and tables are getting large, look into setting up some indexes and watch the difference.