User feedback regarding a CMS web application was that the certain areas of the site were running slow. There could be many reasons for this like the usual suspects of a slow internet connection, poorly optimized images and stylesheets, and large page sizes. In this case there was a large amount of data coming from a couple tables in the database and were combined together to render the page. This was slowing the page load time down as the MySQL database was combining this data. This is where a simple optimization technique called indexing can help.

Databases are optimized to quickly store and retrieve information. They however, are pretty stupid when it comes to quickly searching on that information. They treat all columns of data as equals by default. In order to optimize a database for a particular application you look at what columns are searched on regularly and create an index of that column. In this case we wanted to index what is called a foreign key or the lookup value that is common between the two tables. For illustrative purposes consider the two tables below that share the page-name field.

If we want to optimize the performance of getting all tags for a particular page, we would create an index on page-name in the “Page Tags” table. This can be done in phpMyAdmin by clicking on the “index” button (with the lightning symbol on it).

When you index a column you do take a slight performance hit when you insert data into that table since it needs to re-index the column. So you don’t want to go and index every column. Probably the best way to test is to index what you think is the right column and see if performance improves. If it does leave the index. If it doesn’t scroll down a bit in phpMyAdmin and remove it.