ColdFusion Database Indexes

The most common problem I've run into with applications slow performance is the lack of indexes on the databases. Sometimes this is just because the application ran fine with a small amount of data but now is working off a larger amount, or the application is being used in unexpected ways (running less 'common' queries run more often), or the production environment doesn't have the same indexes as the development environment or worse case the indexes were never created in the first place.

Without indexes queries can take seconds with indexes they take milliseconds!

But how do you know what column to put an index on? If you do the following:

  • Put an index every column used in a join
  • Put an index on every column that's compared with another column or compared to parameter
  • Put an index on every column that you sort by or group by
you'll have all the indexes you need.

A few DBA's reading the above will cringe and say that's too many indexes and it will make inserts and updates slow or it will increase the size of the database. Generally web applications; make far more reads than updates/inserts on the databases, the databases are small to medium in size and hard disk space is cheap!

Indexes are easy to create.

You can create indexes via SQL like so:

view plain print about
1create index <index name> on <table name>(<column name>)

And you can also create indexes via the tag in a ColdFusion page:

view plain print about
1<cfquery name="createindex">
2create index <index name> on <table name>(<column name>)

Make sure the SQL script or ColdFusion page it run as part of the build processes that way you know the live application has all of the correct indexes.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Indexes are good and yes they are cheaper than ever these days.

columns with joins should have indexes generally, the second and third items need to be evaluated by running your query and getting an explain plan to see how the db is working.

If a query returns 10 rows out of a million row table and then you set a condition on those results, there's no need for an index.

Often it will be faster to not use an index for some queries... it's called the 80/20 rule.. which is now often more like 90/10 rule. If you are going to retrieve more that 10 of the table, often an index may be slower than a full table scan

Sometimes badly designed schema's confuse the db engine as well.

Oracle has materialised views which are basically a view as a table which you can then index
# Posted By zac spitzer | 5/12/07 2:20 PM
You're right about the problem that if you put too many indexes on a table, it will slow down the table considerably during inserts and updates. This is because SQL must update all the indexes that are affected by the columns being inserted or updated. Also having too many indexes on your table can cause index fragmentation. Most of the time you can defrag your indexes, but in extreme cases you will have to drop and recreate them. This could cause your entire database to slow to a crawl depending on the size of the table and what since SQL has to LOCK the table to create the index.

Make sure that you have indexes on columns that are going to be joined on. I would go even further and make sure that these columns are clustered indexes. Since you can only have a clustered index per table I would make sure that the clustered index is on the column being the foreign key and not the primary key if the table is the forgein reference of the join and will be getting queried a lot.

If you still find that your database is slow after applying your indexes, I would suggest trying to partition your data using partitioned views. The BOL goes into great details on how and when to use partitioned views.

The last resort of course is caching and replication. Try caching commonly use into the Application scope or using query caching. You can also replicate your database so that one server is handling nothing but selects, while the other handles all the transactions. This can be a pain to administrate though.
# Posted By Tony Petruzzi | 5/12/07 5:24 PM
Even more, you can run a sql script that will tell you the potentially useful indexes ( or a script that will list any index not used since the last time SQL Server was recycled (, etc.
# Posted By Ed | 5/12/07 6:48 PM
Hi Zac.

True you can look at execution plans to work out what indexes you need, however it may not show you what indexes you need if your application changes in some way ie load suddenly increase or the application is used in a different way than expected. Personally I think it's better to add the indexes that may be required so you don't run into issues in the future.

It's rare for an index to slow down a query and if it does it will only be a small performance cost, without the correct indexes queries can be several order of magnitude slower, so again it's generally better to have them than not.

Thanks for the comment.
# Posted By Justin Mclean | 5/13/07 10:11 AM
Hi Tony,

Generally web application do far more selects than inserts/updates so the cost of update/insert is not normally an issue.

Views, replication, caching, partitioning etc are all good and can give you better performance. Generally I look at indexes first and then if it's still an issue look into other solutions.
# Posted By Justin Mclean | 5/13/07 10:16 AM
Hi Ed,

Very helpful if you're using SQLserver.
# Posted By Justin Mclean | 5/13/07 10:17 AM