Adobe ColdFusion 8 Web Application Construction Kit, Volume 1: Getting Started

Using Indexes

Now revisit your movies database. Movie production is up, and the number of movies in your movies table has grown, too. Lately, you’ve noticed that database operations are taking longer than they used to. The alphabetical movie list report takes considerably longer to run, and performance drops further as more movies are added to the table. The database design was supposed to be a scalable solution, so why is the additional data bringing the system to its knees?

The solution here is the introduction of additional indexes. The database application automatically creates an index for the primary key. Any additional indexes have to be explicitly defined. To improve sorting and searching by rating, you just need an index on the rating ...