How to optimize SQL queries (part 2)

Tuesday, 08/12/2020

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

<span class="token keyword">SELECT</span> … <span class="token keyword">FROM</span> … <span class="token keyword">WHERE</span> … <span class="token keyword">PROCEDURE</span> ANALYSE <span class="token punctuation">(</span> <span class="token punctuation">[</span> max_elements <span class="token punctuation">,</span> <span class="token punctuation">[</span> max_memory <span class="token punctuation">]</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span>

SELECT … FROM … WHERE … PROCEDURE ANALYSE ( [ max_elements , [ max_memory ] ] )

// Example

 <span class="token keyword">SELECT</span> col1 <span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> table1 <span class="token keyword">PROCEDURE</span> ANALYSE <span class="token punctuation">(</span> <span class="token number">10</span> <span class="token punctuation">,</span> <span class="token number">2000</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span>

SELECT col1 , col2 FROM table1 PROCEDURE ANALYSE ( 10 , 2000 ) ;

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.

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> users <span class="token punctuation">(</span>
  id <span class="token keyword">int</span> <span class="token punctuation">(</span> <span class="token number">5</span> <span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token punctuation">,</span>
  email <span class="token keyword">varchar</span> <span class="token punctuation">(</span> <span class="token number">20</span> <span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token punctuation">,</span>
  name <span class="token keyword">varchar</span> <span class="token punctuation">(</span> <span class="token number">20</span> <span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token punctuation">;</span>

CREATE TABLE users (

id int ( 5 ) NOT NULL AUTO_INCREMENT ,

email varchar ( 20 ) NOT NULL ,

name varchar ( 20 )

) ;

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

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> Persons <span class="token punctuation">(</span>
 PersonID <span class="token keyword">int</span> <span class="token punctuation">,</span>
 <span class="token keyword">Status</span> <span class="token keyword">varchar</span> <span class="token punctuation">(</span> <span class="token number">25</span> <span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token punctuation">;</span>

CREATE TABLE Persons (

PersonID int ,

Status varchar ( 25 )

) ;

Better way

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> Persons <span class="token punctuation">(</span>
 PersonID <span class="token keyword">int</span> <span class="token punctuation">,</span>
 <span class="token keyword">Status</span> <span class="token keyword">enum</span> <span class="token punctuation">(</span> <span class="token string">'Married'</span> <span class="token punctuation">,</span> <span class="token string">'Single'</span> <span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span> <span class="token punctuation">;</span>

CREATE TABLE Persons (

PersonID int ,

Status enum ( 'Married' , 'Single' ) NOT NULL

) ;

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:

/etc/mysql/my.cnf
...
[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

/etc/mysql/my.cnf

...

[mysqld]

query_cache_type=1

query_cache_size = 10M

query_cache_limit=256K

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:

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userid <span class="token operator">=</span> <span class="token number">123</span> <span class="token punctuation">;</span>

select * from user where userid = 123 ;

Better way:

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userid <span class="token operator">=</span> ‘ <span class="token number">123</span> ’ <span class="token punctuation">;</span>

select * from user where userid = ‘ 123 ’ ;

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

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 
  table1 t1 <span class="token keyword">left</span> <span class="token keyword">join</span> table2 t2 
  <span class="token keyword">on</span> t1 <span class="token punctuation">.</span> size <span class="token operator">=</span> t2 <span class="token punctuation">.</span> size 
  <span class="token keyword">where</span> t1 <span class="token punctuation">.</span> id <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token punctuation">;</span>

select * from

table1 t1 left join table2 t2

on t1 . size = t2 . size

where t1 . id > 2 ;

Better way:

<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 
  <span class="token punctuation">(</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> table1 <span class="token keyword">where</span> id <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token punctuation">)</span> 
  t1 <span class="token keyword">left</span> <span class="token keyword">join</span> table2 t2 
  <span class="token keyword">on</span> t1 <span class="token punctuation">.</span> size <span class="token operator">=</span> t2 <span class="token punctuation">.</span> size <span class="token punctuation">;</span>

select * from

( select * from table1 where id > 2 )

t1 left join table2 t2

on t1 . size = t2 . size ;

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.

summary

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:

https://towardsdatascience.com/how-to-optimize-sql-queries-part-ii-407311784112

Share the news now

Source : Viblo