How to optimize SQL queries (part 2)

Tram Ho

In the previous part, I also introduced several ways to optimize queries in SQL. Today, I would like to introduce part 2 on this topic

1. Get help from PROCEDURE ANALYSE ()

PROCEDURE ANALYSE() will let MySQL help you analyze your fields and their actual data and will provide you with some useful hints.

  • For example, if you make an INT field as a primary key, but not a lot of data, then PROCEDURE ANALYZE () will suggest you change this field type to MEDIUMINT

// Example

  • max_elements (default 256) is the maximum number of distinct values max_elements ( ANALYZE () perceives per column.
  • max_memory (default 8192) is the maximum amount of memory ANALYZE () allocates to each column.

2. Always set an ID for each table

We should set ID as the primary key for each table in the database and preferably INT AUTO_INCREMENT type.

  • Using the VARCHAR type as a primary key reduces performance
  • Furthermore, in the MySQL database, there are still some operations that require the use of the primary key.

3. Use ENUM instead of VARCHAR

If you have some data fields like “gender” , “status” , “department” … and you know the values ​​of these fields are limited and fixed, then you should use ENUM instead of VARCHAR

Better way

  • The ENUM type is very fast and compact. It saves as TINYINT , but its appearance is displayed as a string
  • In this way, use this field to make the dropdown better.

4. Optimized by cache

Most MySQL servers have enabled query caching. This is one of the most effective ways to improve performance and is handled by the MySQL database engine.

When multiple identical queries are executed multiple times, the results of these queries are placed in a buffer, so that subsequent identical queries will directly access the cached result. No need to go directly to the database to access the tables.

You can enable the buffer query by editing the MySQL configuration file sudo nano /etc/mysql/my.cnf

Add the following options at the end of your file:

5. Usually, the number of indexes should be less than 5

  • The fewer indexes you have, the better, while it improves the efficiency of your queries, they also reduce the efficiency of adding new and updating.
  • Ideally, a table should have no more than 5 indexes, but if there are too many, consider removing some of the unnecessary ones.
  • Index should be set only for permanent fields with little change.

6. If the field type is a string, it must be enclosed in quotation marks

Usually, common, normal:

Better way:

  • When parentheses are not added, it’s a comparison between a string and a number and their types don’t match.
  • MySQL will now convert the default type, and then compare. It will increase the computation.

7. Optimize the temporary table

When creating temporary tables, if you insert large amounts of data at once, you can use select into instead of create table to avoid a large amount of logs to improve speed.

If the amount of data is not large, to reduce the resources of the system table, you should first create table , then insert .

8. When using left join how to make the result of the left table as small as possible

If you want to use left join , the resulting data of the left table is as small as possible. Usually, common, normal

Better way:

  • In inner join , the number of rows returned is relatively small, so performance will be relatively better.
  • Similarly, if left join used, the left-table data result is as small as possible, and the more conditions placed on the left table, it means the number of rows returned can be similar. relatively small.


  • Your preferences should be in the order: inner join, left join.
  • Always put IDs in tables.
  • Use ENUM instead of VARCHAR if your column includes a TINYINT list because ENUM uses TINYINT which improves efficiency.
  • Get advice from PROCEDURE ANALYSE() on using the correct data type.
  • Your string must be enclosed in quotes

Above are a few ways for you to optimize your query, hoping to help everyone.

Reference source:

Share the news now

Source : Viblo