6.1 Introduction

Now let's move from theory to practice.

“In theory, there is no difference between theory and practice. In practice, they are."

We live in the real world and all software products are ultimately created for living people. And these living people are very annoyed by sites that load slowly, and programs that slow down.

And if a database query takes more than a second, this is unacceptable . Users simply won't use a product that has pages/functionality that is so slow.

But often, in order to display one page, you need to perform several dozen queries to the database. And if they are executed sequentially, then you no longer have a second limit, but let's say 100ms per request.

Here are the top 5 ways programmers speed up database queries:

  1. Adding indexes to tables in the database.
  2. Rewriting and optimizing queries.
  3. Enable (and configure) caching on the database side.
  4. Enable caching on the client side.
  5. Performing database denormalization.

You are already familiar with all these things for the most part, so the following will only be practical advice.

6.2 Indices

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

Java webinar

In this article I would like to give practical advice on using MySQL.

I will say right away:

  • this article is written about MySQL, although the general 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.
  • advice does not pretend to be new and is the result of a generalization of the literature read and personal experience.
  • within the framework of this article, I will not touch on MySQL configuration issues.

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

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

Let's take a closer look at each of these groups.

Using Indexes

Not using or misusing indexes is what most often slows down queries. For those who are not familiar with the mechanism of how indexes work or have not yet read about it in the manual, I strongly advise you to read it.

Tips for using indexes:

  • You don't need to index everything . Quite often, without understanding the meaning, people simply index all the fields of a table. Indexes speed up fetches, but slow down row inserts and updates, so the choice of each index must be meaningful.
  • One of the main parameters that characterizes the index is selectivity, which is the number of different elements in the index. It makes no sense to index a field that has two or three possible values. There will be little benefit from such an index.
  • The choice of indexes should begin with an analysis of all queries against a given table. Very often, after such an analysis, instead of three or four indices, you can make one composite one.
  • When using composite indexes, the order of the fields in the index is critical.
  • Don't forget about covering indexes. If all data in a query can be retrieved from an index, then MySQL will not access the table directly. Such requests will be executed very quickly. For example, for a query SELECT name FROM user WHERE login='test'with an index (login, name), access to the table is not required. Sometimes it makes sense to add an additional field to a composite index, which will make the index cover and speed up queries.
  • For row indexes, it is often sufficient to index only part of the row. This can significantly reduce the index size.
  • If %it is at the beginning, LIKE(SELECT * FROM table WHERE field LIKE '%test')indexes will not be used.
  • The FULLTEXT index is only used with the MATCH ... AGAINST syntax .

6.3 Database structure

A well-designed database is the key to fast and efficient work with the database. On the other hand, a poorly designed database is always a headache for developers.

Database design tips:

  1. Use the smallest possible data types. The larger the data type, the larger the table, the more disk accesses are needed to get the data. Use a very convenient procedure: SELECT * FROM table_name PROCEDURE ANALYSE();to determine the minimum possible data types.
  2. Observe normal forms during the design phase. Often programmers resort to denormalization already at this stage. However, in most cases, at the beginning of the project, it is far from obvious how this can result. Denormalizing a table is much easier than suffering from a sub-optimally denormalized one. And JOINsometimes it works faster than incorrectly denormalized tables.
  3. Don't use NULLcolumns unless you consciously need them.

6.4 SQL queries.

Just as often there is a desire to rewrite all queries in native SQL so that the query is as fast as possible. If you decide to do this, then here are some tips:

  1. Avoid requests in a loop. SQL is a language of sets, and writing queries should be approached not in the language of functions, but in the language of sets.
  2. Avoid *(asterisks) in queries. Feel free to list exactly the fields you choose. This will reduce the amount of data fetched and sent. Also, don't forget about covering indexes. Even if you do select all the fields in the table, it's better to list them. First , it improves the readability of the code. When using asterisks, it is impossible to find out what fields are in the table without looking into it. Secondly , today your table has five INT columns, and a month later one more TEXT and BLOB were added , and the asterisk remained as it was.
  3. When paginated, to get the total number of records, use SQL_CALC_FOUND_ROWSand SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, caches the selected number of rows (before LIMIT is applied), and when used, SELECT FOUND_ROWS()only returns this cached value without having to re-execute the query.
  4. Don't forget that there INSERTis a syntax for multiple inserts. One query will run an order of magnitude faster than multiple queries in a loop.
  5. Use LIMITwhere you don't need all the data.
  6. Use INSERT… ON DUPLICATE KEY UPDATE…in place of and INSERTor UPDATEafter selection, and often in place of REPLACE.
  7. Don't forget this amazing feature GROUP_CONCAT. It can help out with complex queries.