|Performance Tips : Indexing your Joomla tables|
|Tuesday, 10 September 2013 23:15|
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 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 trickest 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 column, click on 'Add Index'
It is important to monitor query times when adding indexes. It i 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 canidates for indexing and the columns here that are foreign keys should be indexed.
Jump on to our Newsletter & stay updated on News on our Extensions, New Releases & Offers !