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, thenPROCEDURE ANALYZE ()
will suggest you change this field type toMEDIUMINT
1 2 |
<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> |
// Example
1 2 |
<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> |
max_elements
(default 256) is the maximum number of distinct valuesmax_elements
(ANALYZE ()
perceives per column.max_memory
(default 8192) is the maximum amount of memoryANALYZE ()
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.
1 2 3 4 5 6 |
<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> |
- 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
1 2 3 4 5 |
<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> |
Better way
1 2 3 4 5 |
<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> |
- The
ENUM
type is very fast and compact. It saves asTINYINT
, 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:
1 2 3 4 5 6 7 |
/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:
1 2 |
<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> |
Better way:
1 2 |
<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> |
- 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
1 2 3 4 5 |
<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">></span> <span class="token number">2</span> <span class="token punctuation">;</span> |
Better way:
1 2 3 4 5 |
<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">></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> |
- 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 ofVARCHAR
if your column includes aTINYINT
list becauseENUM
usesTINYINT
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