Optimize the use of temporary tables and improve the performance of SQL statements by 100 times

The SQL statement is as follows:
Select distinct cp. name AS cp_name, c. name AS category_name, t. name AS type_name FROM g LEFT JOIN  cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN  c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN  t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

The DBA observed high IO because the SQL statement generated a huge temporary table and the memory could not be stored. Therefore, all the data was copied to the disk, causing IO to soar.

The first statement is to query the Qualified Data. You only need to query g. id.
SELECT DISTINCT FROM g LEFT JOIN  cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN  c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN  t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;

 g LEFT JOIN  cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN  c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN  t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;

Testing on an SSD machine requires about 7 s before optimization, the first 0.3 s after optimization, and the second 0.1 s after optimization.
We can see that the disk I/O is the performance bottleneck before optimization, and the SSD speed is much faster than SATA. After optimization, the disk is no longer the bottleneck, and there is no difference in SSD and SATA performance.

On the Linux platform, the/tmp directory is used by default. Note that the/tmp directory is small.

3) when UNION or union all is used, the SELECT clause contains columns greater than 512 bytes;

2) split a long column: Generally, TEXT, BLOB, strings larger than 512 bytes are basically used to display information but not for query conditions. Therefore, when designing a table, these columns should be independent to another table.

1) Split SQL statements
Temporary tables are mainly used for sorting and grouping. Many businesses require sorting and then retrieve detailed paging data. In this case, you can split the sorting and detailed data into different SQL statements, this method is used in our case to reduce the size of temporary tables during sorting or grouping and improve the efficiency of sorting and grouping.
2) Optimize Services, remove sorting groups, and other operations
Sometimes businesses do not need to be sorted or grouped, but simply sorted for good looks or reading convenience, such as data export and data query operations, in this case, removing sorting and grouping does not have much impact on the business.