Now and then you might come across a site that has been running for a while that seems to all of a sudden becomes a nightmare to navigate around and functions that were in the past fairly zippy now come to a grinding halt…. So the logical thing in your head to do is to check the subversion history to see if anyone has made a changes, ask around the office, scratch your head, see if any other sites on the same server are running slow etc… However, what you should check is whether or not the tables have been indexed in the database, because without indexes your site is going to run as slow as an old man’s prostate.

Before a site is made live the tables should ALWAYS be indexed.

“But why the heck should I”? I hear you ask – Well the answer is simple:

Whenever you make a request to the CF server that in turn has to make a request to the database, a processing thread is created and continues to live in memory until the end of its execution. If the tables in a given database are not indexed then the time that CF has to wait for the request to come back from a poor performing and slow database server is increased dramatically and it has to keep that thread open until it has finished. Therefore any further requests to the CF server are queued adding extra stress to the whole system. A fast executing database server makes CF happy and able to return and execute a thread in an optimal manner.

“What the heck is an index anyway”? you will probably mumble now, so here:

There are too many articles out on the internet explaining what an index is without having to add one more here so quoting wikipedia – ‘A database index is a data structure that improves the speed of operations in a table. Indices can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.’

“So what can I do”?

Usually the majority of tables in the base farcry install will have the correct indexes in place so there is no need to do anything. But if you start adding custom functionality to the site then you should spend that extra 10 minutes working out where your indexes should live. The general rules for a Farcry site are as follows:

1. Primary keys on objectId on every Column except for Array Tables
2. Index data + objectid (primary in fc4) in array tables
3. Index any column used in a JOIN or WHERE clause

That’s it! if you do this you will probably increase performance by 10 fold, it makes that much of a difference!