The road to using Mysql

One of the most important tasks for those in charge of administering a database system (and also for many developers) is to make sure queries run efficiently. In this first part of several articles on optimising queries, I'm going to show how to establish which queries are taking a long time to execute. This is not always obvious, particularly if you are the administrator of the system and not the user.

The Mysql system uses something called the Slow query log, which can be extremely useful in optimising system performance. However by default, Mysql does not log slow queries. In order to use this log, you need to go into your my.cnf file (this may be in /etc/mysql/ or in /opt/lampp/etc/ if you are using a lampp server). You then need to make sure the following two lines (in the [mysqld] section) are enabled:

Code:

In the above line I have entered 2 (for seconds), you can change this if you wish - I understand the default time in Mysql is 20 seconds. A word about the path-name set for log-slow-queries - if you leave this path out, the error log will be written in your data directory, and the file name will be [systemname]-slow.log. You can find the location of your data directory by calling up mysql and running the following command:

Code:

One more thing. You will need to make sure that a file already exists in /var/log/mysql called mysql-slow.log (use the command touch to create this file if you're using a Unix or Linux machine) and that the file permissions allow users to write to it. All being well, any queries that take place after the mysql server is restarted, will be written to this log if they take longer than the specified period of time.

If you're interested in finding out more on Mysql Server logs, here's the link to the relevant part of the manual:
Mysql manual link

Well, once we've established which queries are taking a long time to complete, we need to see how we can make them run faster. In forthcoming articles I will look at:

* Changing the coding - this can involve a better use of indexes. It can also mean re-writing the query to use a join which is usually far more efficient in Mysql than using sub-queries. I have to admit this has been one of my weak points. I will also show how useful "Explain plan" can be.

* Poor indexing choices and Poor schema design. Sometimes the way you create indexes or design database tables can have a negative impact on performance issues.

One thing I have to point out is that databases change (through necessity) over time, so unfortunately performance may have to be checked over at regular intervals. However, we can go too far in constantly changing things to gain speed. We should be realistic in these matters. Is it worthwhile spending 4 hours of a dba's time to make a query run 1 second quicker, if that query is only run once a day or once a week? Common-sense (as in all things) should be taken into consideration.

Well, I hope to have the next article on query optimisation fairly soon. Until then, enjoy your coding!