Performance Tips: Indexing your Joomla tables

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 - 

1. You should create indexes on columns that are used frequently in WHERE clauses.
2. You should create indexes on columns that are used frequently to join tables.
3. You should create indexes on columns that are used frequently in ORDER BY clauses.
4. You should create indexes on columns that have few of the same values or unique values in the table (i.e. indexing the published/approved columns may not be a good idea).
5. You should not create indexes on small tables (tables that use only a few blocks) because a full table scan may be faster than an indexed query.

Adding Indexes

Go to phpmyadmin and open the structure of a table. Under more tab of the column, click on 'Add Index'
Or
You can add index using the syntax: ALTER TABLE table_name ADD INDEX (column_name)

Performance Considerations

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.