Question:
How to optimise MySQL for Drupal
Here are some basic strategies for optimising Drupal's use of the database.
Enabling MySQL's Query Cache
To enable add the following lines to to your MySQL options file - the file is named my.cnf and specifies the variables and behaviour from your MySQL server.
# The MySQL Server
[mysqld]
query_cache_size=64M
The current cache size can be viewed as output of MySQL's SHOW VARIABLES command:
SHOW VARIABLES LIKE 'query_cache%';
query_cache_size | 67108864
query_cache_type | ON
Update variables on server without reboot
For global variables:
mysql > set global query_cache_size=67108864;
mysql > set global max_connections=200;
For others:
mysql > set @@long_query_time=10;