Eliminating unnecessary internal temporary tables

I can’t stress enough that people look at SQL statements that are being executed against your production MySQL database, and you optimize queries when you can.

Often it’s the improvement to the large number of similar queries executed that can optimize resources. In this example, we take a very simple query, and by removing an unnecessary order by, we eliminate MySQL internally creating a temporary (in memory) table.

So what’s the big deal.

  • The query is simpler to read and understand
  • Memory required for the connection is not assigned
  • A number of internal steps are no longer required (4 of 21 logging messages, not an ideal measurement, but an indication). In this case, it was easily a 10% performance improvement for each query.

This query is executed 10-100 times per second, so the improvement in performance is significant.

mysql> explain select max(mdate) as mdate from tbl  where original_account = 'xxx@msn.com' and id = '15847' order by mdate desc;
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+------------------------------+
| id | select_type | table                | type | possible_keys        | key              | key_len | ref         | rows | Extra                        |
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+------------------------------+
|  1 | SIMPLE      | tbl                  | ref  | ids,original_account | original_account | 388     | const,const |  146 | Using where; Using temporary |
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+------------------------------+
1 row in set (0.00 sec)
mysql> explain select max(mdate) as mdate from tbl  where original_account = 'xxx@msn.com' and id = '15847';
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+-------------+
| id | select_type | table                | type | possible_keys        | key              | key_len | ref         | rows | Extra       |
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tbl                  | ref  | ids,original_account | original_account | 388     | const,const |  146 | Using where |
+----+-------------+----------------------+------+----------------------+------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> show profile cpu,memory,source for query 1;
+--------------------------------+----------+----------+------------+---------------------------+---------------+-------------+
| Status                         | Duration | CPU_user | CPU_system | Source_function           | Source_file   | Source_line |
+--------------------------------+----------+----------+------------+---------------------------+---------------+-------------+
| (initialization)               | 0.00001  | 0        | 0          | send_result_to_client     | sql_cache.cc  |        1143 |
| checking query cache for query | 0.000079 | 0        | 0          | open_tables               | sql_base.cc   |        2652 |
| Opening tables                 | 0.000024 | 0        | 0          | mysql_lock_tables         | lock.cc       |         153 |
| System lock                    | 0.000015 | 0        | 0          | mysql_lock_tables         | lock.cc       |         163 |
| Table lock                     | 0.000041 | 0        | 0          | mysql_select              | sql_select.cc |        2282 |
| init                           | 0.000046 | 0        | 0          | optimize                  | sql_select.cc |         765 |
| optimizing                     | 0.000027 | 0        | 0          | optimize                  | sql_select.cc |         924 |
| statistics                     | 0.000173 | 0        | 0          | optimize                  | sql_select.cc |         934 |
| preparing                      | 0.000028 | 0        | 0          | optimize                  | sql_select.cc |        1383 |
| Creating tmp table             | 0.000053 | 0        | 0          | exec                      | sql_select.cc |        1603 |
| executing                      | 0.000011 | 0        | 0          | exec                      | sql_select.cc |        1743 |
| Copying to tmp table           | 0.002226 | 0        | 0          | exec                      | sql_select.cc |        2123 |
| Sending data                   | 0.000148 | 0        | 0          | mysql_select              | sql_select.cc |        2327 |
| end                            | 0.000013 | 0        | 0          | free_tmp_table            | sql_select.cc |       10115 |
| removing tmp table             | 0.000064 | 0        | 0          | free_tmp_table            | sql_select.cc |       10143 |
| end                            | 0.000014 | 0        | 0          | mysql_execute_command     | sql_parse.cc  |        5154 |
| query end                      | 0.000012 | 0        | 0          | query_cache_end_of_result | sql_cache.cc  |         735 |
| storing result in query cache  | 0.000047 | 0        | 0          | mysql_parse               | sql_parse.cc  |        6155 |
| freeing items                  | 0.000021 | 0        | 0          | dispatch_command          | sql_parse.cc  |        2146 |
| closing tables                 | 0.000014 | 0        | 0          | log_slow_statement        | sql_parse.cc  |        2204 |
| logging slow query             | 0.000011 | 0        | 0          | dispatch_command          | sql_parse.cc  |        2169 |
+--------------------------------+----------+----------+------------+---------------------------+---------------+-------------+
21 rows in set (0.00 sec)


mysql> show profile cpu,memory,source for query 2;
+--------------------------------+-----------+----------+------------+---------------------------+---------------+-------------+
| Status                         | Duration  | CPU_user | CPU_system | Source_function           | Source_file   | Source_line |
+--------------------------------+-----------+----------+------------+---------------------------+---------------+-------------+
| (initialization)               | 0.000021  | 0        | 0          | send_result_to_client     | sql_cache.cc  |        1143 |
| checking query cache for query | 0.000090  | 0        | 0          | open_tables               | sql_base.cc   |        2652 |
| Opening tables                 | 0.000022  | 0        | 0          | mysql_lock_tables         | lock.cc       |         153 |
| System lock                    | 0.000014  | 0        | 0          | mysql_lock_tables         | lock.cc       |         163 |
| Table lock                     | 0.000044  | 0        | 0          | mysql_select              | sql_select.cc |        2282 |
| init                           | 0.000049  | 0        | 0          | optimize                  | sql_select.cc |         765 |
| optimizing                     | 0.000028  | 0        | 0          | optimize                  | sql_select.cc |         924 |
| statistics                     | 0.000179  | 0        | 0          | optimize                  | sql_select.cc |         934 |
| preparing                      | 0.000029  | 0        | 0          | exec                      | sql_select.cc |        1603 |
| executing                      | 0.000016  | 0        | 0          | exec                      | sql_select.cc |        2123 |
| Sending data                   | 0.00229   | 0        | 0          | mysql_select              | sql_select.cc |        2327 |
| end                            | 0.000039  | 0        | 0          | mysql_execute_command     | sql_parse.cc  |        5154 |
| query end                      | 0.000012  | 0        | 0          | query_cache_end_of_result | sql_cache.cc  |         735 |
| storing result in query cache  | 0.000011  | 0        | 0          | mysql_parse               | sql_parse.cc  |        6155 |
| freeing items                  | 0.00002   | 0        | 0          | dispatch_command          | sql_parse.cc  |        2146 |
| closing tables                 | 0.000014  | 0        | 0          | log_slow_statement        | sql_parse.cc  |        2204 |
| logging slow query             | 0.00001   | 0        | 0          | dispatch_command          | sql_parse.cc  |        2169 |
+--------------------------------+-----------+----------+------------+---------------------------+---------------+-------------+
17 rows in set (0.00 sec)
Tagged with: Databases General MySQL Open Source

Related Posts

How long does it take the ReadySet cache to warm up?

During my setup of benchmarking I run a quick test-sysbench script to ensure my configuration is right before running an hour+ duration test. When pointing to a Readyset cache where I have cached the 5 queries used in the sysbench test, but I have not run any execution of the SQL, throughput went up 10x in 5 seconds.

Read more

Monitoring Latency with Throughput

Higher throughput does not imply improved performance. This is a common problem when the need for an application to support more users, you provide higher concurrency and that appears to show the capability to support higher throughput.

Read more

Using Readyset Caching with AWS RDS MySQL

Readyset is a next-generation database caching solution that offers a drop-in; no application code changes; approach to improve database performance. If you are using a legacy application where it is difficult to modify SQL statements, or the database is overloaded due to poorly-designed SQL access patterns, implementing a cache is a common design strategy for addressing database reliability and scalability issues.

Read more