A Quick Way to Optimize a Joomla Website: Logging MySQL Queries that Lack Indexes

If you have already optimized many of the slow queries on your Joomla website using the power of the MySQL slow query log, and yet MySQL is still putting some pressure on your server load, then a good idea would be to check which indexless queries are being executed by your database server. By indexless queries, we mean those queries that have filters or sorting, but the fields that these queries are filtered or sorted by are not indexed.

For example, if you have the following query somewhere on your Joomla website:

SELECT * FROM #__content WHERE cat_id=5 ORDER BY created DESC";

Then you should have both cat_id and created indexed.

Now the question is, how do you know easily which queries lack indexing? Well, luckily, there is an easy way to do it in MySQL if you have root access to the server.

All you need to do is the following:

  • ssh to the server hosting the database of your Joomla website.
  • Open the file my.cnf located under the /etc folder (that file might be located elsewhere, but on all the servers we have worked on, it was located under the /etc folder).

  • Add the following lines at the end of the file:

    slow-query-log=1
    long-query-time=1
    log_queries_not_using_indexes=1
    slow-query-log-file="/var/lib/mysql/slow.log"

  • Save the file (if you’re using vi you can quickly save the file by clicking on ESC and then typing :wq! followed by the ENTER key).

  • Restart MySQL using the following command:

    /etc/init.d/mysql restart

  • Now keep an eye on the slow.log file located under the /var/lib directory, you will notice that it will get filled with all sorts of queries, quickly!

Once you have enough queries then you should disable indexless query logging the following way:

  • Open the file my.cnf under the /etc folder.
  • Comment out the following line by adding a sharp (or a hash – depending on what you call it) to its beginning:

    log_queries_not_using_indexes=1

    (so the above line will be #log_queries_not_using_indexes=1)

  • Save the my.cnf file and restart MySQL.

Now you can examine all the indexless queries in the slow.log file and fix them. You should focus first on the queries that you see most in the slow.log file. Once you fix those queries, repeat the process above (re-enable/re-disable the logging of the queries that have no indexes) until all (or most) of these queries are fully optimized. Now check your load, and you should see a substantial difference.

Note that you will need to have some serious database skills to fix those queries. If you need help, then you can always contact us. Our rates are super affordable, our work is very clean and professional, and we are courteous and super fun to work with!

No comments yet.

Leave a comment