Duplicate Indexes Can Really Slow Down Your Joomla Website

Note: This post is extremely advanced and is geared towards those who are well versed in database administration and who have a vast knowledge of the ins and outs of Joomla. If that doesn’t apply to you, then please do not experiment with your live site (you may make things worse, much worse), and contact some professionals, such as, ahem, itoctopus!

Another note: If the MySQL Slow Query Log is showing that this query…

UPDATE #__content SET hits=n WHERE id=m

…is taking a long time to execute then this post will definitely help!

Every new programmer is taught that table indexing is good for you – and that he should ensure that all the filter fields in all the tables are properly indexed. So, that programmer grows into thinking that the more indexes he has on a table, the faster that table is. This is wrong…

In fact, the more indexes you have on a certain table, the slower any updates/inserts on that table are. Additionally, if you have duplicate indexes, then the performance hit on updates/inserts can be dramatic. Not only that, the performance gain from duplicate indexes in read only table activities (such as SELECT queries) is this: none, zilch, zero, nada! (There are some exceptions to this though.)

The thing is, detecting duplicate indexes can be a painful job, and requires some serious expertise in database administration. Of course, there are tools that can detect duplicate indexes, but we are assuming that you don’t have access to these tools.

So how can you manually detect duplicate indexes?

Before detecting duplicate indexes on a table, you will first need to check the indexes on that particular table. This can be done in 2 ways:

  1. Clicking on the Indexes link just below the table structure in the Structure view of that table in phpMyAdmin.

  2. Running the following query:

    SHOW INDEX FROM [your-joomla-table];

    For example:

    SHOW INDEX FROM #__categories

    where #__ is your Joomla table prefix as defined in the configuration.php file located in the root directory of your Joomla website.

Now, once you’re able to see the indexes on that table, you will need to visually check for duplicates. Of course, the big question is, how?

Well, an obvious duplicate index is when you have the same field indexed twice in an identical index. An example of this scenario is when you have these 2 indexes on the same table:

  • idx_left: Which solely consists of the left column.

  • idx_left_2: Which also solely consists of the left column.

Of course, it is super easy to detect such a duplicate index. A more complicated one to detect (and fix) is when you have the index consisting of more than one column. For example, let’s say you have the following indexes on your table:

  • idx_title: Which solely consists of the title column.

  • idx_title_published: Which consists of the title and the published columns.

  • idx_title_alias_published: Which consists of the 3 columns: title, alias, and published.

Now, before explaining the duplication in the above, let us very quickly explain how indexing works if there are multiple columns in the index: in short, an index is created on the first column, and then an index is created on the first column and the second column, and then an index is created on the first column, second column, and the third column, etc…

For example, in our index idx_title_alias_published above, the following columns are indexed: title/title, alias/title, alias, published. As you can see, the index idx_title is clearly a duplicate, because we already have an index on the title column as part of the idx_title_alias_published index. So, we can safely drop that index by issuing the following query (or by clicking on Drop next to the index name in phpMyAdmin):

ALTER TABLE [your-joomla-table] DROP INDEX `idx_title`

But, how about the idx_title_published index. Clearly the first index created (which is the index on the title column) is a duplicate in the idx_title_alias_published index , but the second index, which is the index on the title and the published columns, is not. Now, since there’s a 99.99999% chance that we are only interested in the idx_title_alias_published index because of the index on the 3 columns combined (e.g. the index on the title, the alias, and the published fields), then we can modify that index to be idx_title_published_alias, where the order of the fields in that index is: title, published, and alias. If we do this, then both indexes created by the idx_title_published will be included, which means we can safely delete it (e.g. we can safely delete the idx_title_published index).

Of course, the examples we gave above are simplistic, and the indexes can be much more complicated than that (keep in mind also, that in some cases, duplicate indexes are a necessity, but that’s probably in about 2% of the cases). If you are having some performance issues on your Joomla website because of duplicate indexes, then please contact us. We would love to help and we won’t charge you much.

A final thought: Table indexing is an art, so the artist title should be bestowed on whoever masters that art.

No comments yet.

Leave a comment