Database performance is an advanced topic, and fixing issues relating to the database or specific queries often requires deep knowledge of the application framework, and how databases work. This article provides some insight into best practices for achieving good database performance, and how to identify problems.
Best practices
There are a couple of best practices to keep in mind:
Keep Number of Queries at a Minimum
You should always strive to keep the number of queries for a single page load at a minimum. Any webpage that makes several hundred (or thousands of) queries for a single page load will not scale. It may work on a single developer’s computer, but with multiple concurrent users it will consume computing resources in a fashion that will either cause locking or exhaustion of resources. Either way, such conditions will make your web application fail or underperform.
Make sure Queries use Indexes properly
Proper use of database indexes is vital for good performance and scalability. Missing indexes will make even the simplest queries painfully slow, consuming way too much computing power.
Limit the Result Sets
You usually need only 10 or 50 results from a query, so make sure to make use of SQLs features to narrow down and limit the length of result sets. Big results sets are hard to sort and also hard to post-process in PHP. Don’t ask for 10 000 rows when you only need 10!
Identifying Problems
Use the following steps to help identify problems:
Web Application Debugging
Most web applications are built using a CMS or E-commerce framework. There usually are plugins or extensions available that can log and output the specific queries that are run for any web page. Google terms like query logger, query analyser + your CMS name – and get it installed.
The overview of queries and their performance for any specific page (front, category, product, posts etc) can then be used to identify and fix the biggest problems.
mysqld.slow.log
The slow log is usually off limits for developers in live environments, but your hosting provider will usually let you know if there are problems with slow queries.
Single slow queries can usually be ignored. Any regularly repeating slow query should be examined, especially if they relate to the front end of your web application. Back end actions like building reports can typically also post slow log entries, but do no harm as they are not executed on a high volume basis.
Debugging a Live Environment using Observation
It can be hard to identify the specific queries that are causing problems with the methods provided above, because the behaviour of a web page in a live environment often differs from a controlled development environment.
Observation of the live environment is a very effective way of identifying specific queries that are underperforming and/or queries that are executed at high volumes.
In phpMyAdmin there is a Database server Process overview that will list any currently executing processes. Log in to phpMyadmin with your database credentials and navigate to Server: MariaDB server > Status > Processes.
If the list is empty, or just displays empty processes – hit the refresh button. If you are aware of a specific page that is loading slowly, trigger a reload of that page and hit the refresh button right after, and it will display a list of the currently executing queries. You will need to repeat this manual process many times, and keep notes of the queries.
The query above has the status «Copying to tmp table» which is a strong indication that this query needs examination. Any frequently repeating queries that show up in the list should be examined manually, cut and paste the SQL and put them in a list for later examination.
Analysing SQL queries, use of EXPLAIN
When you have a list of queries, the SQL command EXPLAIN will help you understand how the database executes a query. Select your database, paste in a query in the SQL tab and write EXPLAIN in front and hit execute.
EXPLAIN provides a list of information about how tables are joined, what indexes it can use, what indexes it uses, how many results it returns – and if the database does any heavy lifting like using where, temporary tables or filesort.
Watch out for big result sets, missing indexes, filesort and temporary tables!
