To optimize queries performance in web apps, MySQL Tuning can be a great help! Check here some good practices to apply to your projects.
Table of contents
2.1. Schema design
2.2. Use EXPLAIN
2.3. Understanding EXPLAIN result
2.4. Business logic
2.5 Configuration changes
3. Where to learn more
During development, details related to performance and scalability are usually forgotten (project deadlines are complicated, we know…). Even experienced developers can fail at this point. Slow queries are a regular problem encountered in many web applications.
Probably you have a cache system to reduce database workload, but even with this kind of measure it may not be enough.
In order to help you, below are some best practices that you can use to check if your queries are optimized and we will show you what you can do to improve query performance.
This article was written assuming you use a MySQL 5.7 database, but these tips are also useful for any other RDBMS1.
When you create a foreign key for a given column, an index is automatically created, however, for other cases you should consider creating an index to speed some columns queries. This does not imply you must create indexes for all columns (indexes imply memory and disk space consumption).
Indexes are updated after each insert, update or delete (so your write operations will be affected). Problems related to bad indexation have a heavy cost after your application scales.
To decide if an index is really needed, execute EXPLAIN command.
|id||The SELECT identifier|
|select_type||The SELECT type|
|table||The table for the output row|
|partitions||The matching partitions|
|type||The join type|
|possible_keys||The possible indexes to choose|
|key||The index actually chosen|
|key_len||The lenght of the chosen key|
|ref||The columns compared to the index|
|rows||Estimate rows to be examined|
|filtered||Percentage of rows filtered by table condition|
We will detail only the most important results from above:
The type of SELECT, which can be any of those shown in the following table (listed only the most important ones):
|SIMPLE||Simple SELECT (not using UNION or subqueries)|
|UNION||Second or later SELECT statement in a UNION|
|DEPENDENT UNION||Second or later SELECT statement in a UNION, dependent on outer query|
|UNION RESULT||Result of a UNION|
|SUBQUERY||First SELECT in subquery|
|DEPENDENT SUBQUERY||First SELECT in subquery, dependent on outer query|
The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.
If key is null, you should create an index for one or more columns. See explanation for “rows” below.
The key_len column indicates the length of the key that MySQL decided to use. The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.
The rows column indicates the number of rows MySQL believes it must examine to execute the query. This number should be much lower than total results to avoid a full table scan.
If your “keys” result is null, probably this result is equal to the total results.
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred.
This value indicates how MySQL resolves the query. Be careful with queries using “ORDER BY” clause. For instance, if you see “Using where; Using filesort”.
According to MySQL documentation:
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
The key used to fetch the rows is not the same as the one used in the ORDER BY.
If you need to order results by some columns, be sure you have an index using those columns.
Don’t use DML2 statements inside cycles. For instance, to insert several records records into a table, create a single insert with the values. Depending on the number of records, you should insert records in chunks with multiple transactions to avoid errors like “Lock wait timeout exceeded; try restarting transaction“.
Avoid building results using several simple queries, instead use UNION. Probably if you use an ORM3, you cannot use UNION or other SQL features.
In those cases, we recommend you use a native query (prepared statement obviously) instead of using the query builder.
Before considering this step, make sure you try the query optimization suggested above.
This should only be used if none of the previous steps help.
Related to tables using “InnoDB” engine (this value should be 70% of the available RAM).
This parameter lets you set the maximum size of a sendable packet. A packet is a single SQL state, a single row being sent to a client, or a log being sent from a master to a slave.
This parameter sets the maximum amount of concurrent connections.
RDBMS1: Relational database management system
DML2: Data manipulation language: INSERT, UPDATE, DELETE
ORM3: Object relational-mapping (Doctrine, Eloquent, etc)