MySQL optimization

It's no secret that working with a database takes up most of the work of almost any site. And it is the work with the database that most often is the bottleneck of web applications.

In this article I would like to give practical advice on the use of MySQL.

Immediately make a reservation:

• This article is written about MySQL, although common things are likely to be true for any DBMS.
• Everything written in the article is my personal point of view, and is not the ultimate truth.
• The councils do not claim to be new and are the result of a synthesis of read literature and personal experience.
  
  
• In this article, I will not deal with MySQL configuration issues.

Problems using MySQL can be divided into the following three groups (in order of importance):

1. Non-use or misuse of indexes.
2. Incorrect database structure.
3. Incorrect \ non-optimal SQL queries.

Let us dwell on each of these groups in more detail.



The use of indexes.

Non-use or misuse of indexes is what most often slows down queries. For those who are not familiar with the mechanism of the index or have not read about it in the manual, I strongly advise you to read .

Tips for using indexes:

• No need to index everything. Quite often, without understanding the meaning, people simply index all the fields in the table. Indexes speed sampling, but slow down inserts and row updates, so the choice of each index should be meaningful.
• One of the main parameters characterizing the index is selectivity - the number of different elements in the index. It makes no sense to index the field in which two or three possible values. The benefits of such an index will be small.
• The choice of indexes should begin with an analysis of all queries to this table. Very often, after such an analysis, instead of three or four indices, it is possible to make one composite.
  
  
• When using composite indices, the order of the fields in the index is decisive.
  
  
• Do not forget about covering indexes. If all the data in the query can be obtained from the index, then MySQL will not access the table directly. Such requests will be executed very quickly. For example, for a SELECT name query FROM user WHERE login = “test”, if there is an index (login, name), no access to the table is required. Sometimes it makes sense to add an additional field to the composite index, which will make the index cover and speed up queries.
• For row indices, it is often sufficient to index only a portion of the row. This can significantly reduce the size of the index.
  
  
• If% is at the beginning of the LIKE (SELECT * FROM table WHERE field LIKE '% test') indexes will not be used.
• The FULLTEXT index is used only with the MATCH ... AGAINST syntax.

DB structure.

Competently designed database - a pledge of fast and efficient work with the base. On the other hand, a badly designed database is always a headache for developers.

Database Design Tips:

• Use the lowest possible data types. The larger the data type, the larger the table, the more disk accesses are needed to retrieve data. Use a very convenient procedure: SELECT * FROM table_name PROCEDURE ANALYSE (); to determine the lowest possible data types.
  
  
• At the design stage, follow normal forms. Often, programmers resort to denormalization already at this stage. However, in most cases at the beginning of the project it is far from obvious what this can lead to. Denormalizing a table is much easier than suffering from a suboptimally denormalized table. Yes, and sometimes the JOIN works faster than incorrectly denormalized tables.
  
  
• Do not use NULL columns unless you consciously need them.

SQL queries.

• Avoid querying in a loop. SQL is a language of sets and the writing of queries must be approached not by the language of functions, but by the language of sets.
• Avoid * (asterisks) in queries. Do not be lazy to list exactly the fields that you choose. This will reduce the number of selectable and forwarding data. In addition, do not forget about covering indexes. Even if you really select all the fields in the table, it is better to list them. First, it improves the readability of the code. When using asterisks, it is impossible to find out which fields are in the table without looking into it. Secondly, today there are five INT columns in your table, and in a month one more TEXT and BLOB was added, and the asterisk was, as it was, and remained.
• For paged selection, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS () to get the total number of records; When using SQL_CALC_FOUND_ROWS, MySQL caches the selected number of rows (before using LIMIT) and with SELECT FOUND_ROWS () only returns this cached value without having to re-execute the query.
• Don't forget that INSERT has syntax for multiple inserts. One query will be executed an order of magnitude faster than multiple queries in a loop.
• Use LIMIT where you don't need all the data.
  
  
• Use INSERT ... ON DUPLICATE KEY UPDATE ... instead of selecting and INSERT or UPDATE after it, and often instead of REPLACE.
  
  
• Do not forget about the great function GROUP_CONCAT. She can help out with complex queries.