The Techjoomla Blog

Stay updated with all the latest happenings at Techjoomla. From news about the developments in your favourite extensions to Tips & Tricks about the Joomla CMS, Framework & Development.

Posted by on in Joomla Development
  • Font size: Larger Smaller
  • Hits: 39260
  • Comments

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 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 - 

  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 column, click on 'Add Index'
Or
You can add index using syntax : ALTER TABLE table_name ADD INDEX (column_name)

Performance Considerations

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 

Incase 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.

Rate this blog entry:
Trackback URL for this blog entry.

Ashwin is the Lead geek at Techjoomla. The CTO so to say :) Ashwin has been dabbling with Joomla since the Mambo days. He knows his way around PHP Frameworks - Wordpress, Drupal, Magento as well any kind of coding in general just as well as he knows Joomla. The Guy to go to when anyone is faced with a tech challenge, its also his job to make sure the code getting shipped is top notch !


Ashwin loves to travel, eat, cook and of course speak at Joomla Conferences! 

blog comments powered by Disqus