Optimizing MySQL, Intermediate results

Configuration options, that made huge difference, comparing to the default values

1. Query cache must be enabled and configured

query_cache_type=1        
query_cache_limit=1M
query_cache_size=256M
query_cache_min_res_unit=512

2. Various read and sort buffers must be set

key_buffer_size=128M
join_buffer_size=8M
myisam_sort_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=4M 
read_rnd_buffer_size=4M

3. Set InnoDB tables to be stored each in separate file

innodb_file_per_table=1

4. Tweak MyISAM table

myisam_use_mmap=1

Configuration options, that help to run more complex queries

1. Let MySQL have larger in-memory temporary tables

tmp_table_size=256M
max_heap_table_size=256M

2. Tweak InnoDB engine

innodb_buffer_pool_size=134217728
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8 
innodb_use_sys_malloc=1
innodb_buffer_pool_size=128M

3. Additional tweaks – open file limit, skip name resolve, maximum size of a single packet, number of opened tables to keep in memory

open_files_limit=50000
skip-name-resolve
table_open_cache=4096
table_definition_cache=4096

Combined my.cnf, ready to be copied and deployed

[mysqld]
query_cache_type=1        
query_cache_limit=1M
query_cache_size=256M
query_cache_min_res_unit=512

key_buffer_size=128M
join_buffer_size=8M
myisam_sort_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=4M
read_rnd_buffer_size=4M 

innodb_file_per_table=1

myisam_use_mmap=1

tmp_table_size=256M
max_heap_table_size=256M

innodb_buffer_pool_size=134217728
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_use_sys_malloc=1
innodb_buffer_pool_size=128M

open_files_limit=50000
skip-name-resolve
table_open_cache=4096
table_definition_cache=4096