Once in a while, all of us come across a site that still feels a bit laggy even after doing all the caching & gzipping. Adding appropriate indexes to Joomla's tables can improve query times, effectively decreasing page load times. Depending on your table structure and the amount of data, indexing could mean massive to a slight performance improvement! Practically, indexes are a type of tables, which keep the primary key or index field and a pointer to each record into the actual table.
To start with, you need to set up your development server to log the queries not using indexes. To do this, add this to your MySQL server's my.cnf
log_slow_queries = 1 slow_query_log_file = /var/log/mysql/slow.log log-queries-not-using-indexes
Choosing columns to index
Now, this is the trickiest part. While there's no steps 1-2-3 on this, a rule of thumb (from IBM's SQL guidelines) can be -
Go to phpmyadmin and open the structure of a table. Under more tab of the column, click on 'Add Index'
You can add index using the syntax: ALTER TABLE table_name ADD INDEX (column_name)
It is important to monitor query times when adding indexes. It is possible that adding indexes can have a negative impact on query times. Another important thing to note is that indexes will marginally slow down your inserts since the index gets updated on every insert. So it's important that you don't go overboard with your indexes and keep your tables as clean as possible.
Applying this to Joomla
In case of Joomla, most of the stock tables do not have indexes and adding a few can improve things. The session, user, groups, content, categories, extension & menu tables are excellent candidates for indexing and the columns here that are foreign keys should be indexed.