Re: optimize tmp_table_size and max_heap_table_size

[Peter Brawley]

Read about max_heap_size at https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_heap_table_size. It does need to be tuned but it's not usually a cause for query slowdowns.

The usual causes are query design and other resource tunings. The first thing to do is to enable the slow query log, and for each query it reports after a day or two, run Explain on it and correct the query so it uses indexes and other resources efficiently. You'll need to read about Explain in the manual. When you get stuck, post the query, the Explain result, and the result of Show Create Table of every table references by the query.