MySQL copying to TMP table on disk affects performance

After the data found that MySQL can use variable tmp_table_size and max_heap_table_size to control the upper limit of memory table size, if more than the upper limit will write data to disk, which will have physical disk read and write operations, resulting in performance impact.
We can improve performance by adjusting the values of these two variables (assuming, of course, that the MySQL server has enough memory).
You can first view the current variable values by using the following statement:
Show VARIABLES like ' max_heap_table_size% ';
Then through set GLOBAL max_heap_table_size=522715200; Set the variable value to 512M, you can set the appropriate value according to your own situation; Tmp_table_size variables are set in the same way.
Show VARIABLES like ' tmp_table_size ';
SET GLOBAL max_heap_table_size=512*1024*1024;
Second, developers write statements that are not available to the cache at all.
# time:140901 16:26:23
# User@host:wealth[wealth] @ [172.20.1.70]
# query_time:2266.887211 lock_time:0.000196 rows_sent:17 rows_examined:71501657
SET timestamp=1409559983;
SELECT A.pid,count (a.id) as count,p.productid,p.productname,p.producttype,p.profit,p.starttime,p.endtime,p. Startmoney,p.addtime,i.issuername from ' issuer ' as I, ' Count ' as a left join Product as P on A.pid=p.productid WHERE a.stime > ' time () –3600*24*30′and P.issuerid=i.issuerid and p.isdel=0 Group by A.pid Order by Count (a.id) desc limit 0, 17;
Ah ~ ~ No way, this SQL optimization is a long-term process