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
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:
And you can also create indexes via the
2create index <index name> on <table name>(<column name>)
3</cfquery>
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.
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
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.
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.
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.
Very helpful if you're using SQLserver.