MySQL server sql tuning.

1. When you need to tune MySQL sql clauses first thing to do is set slow query and general query logs on:

Slow query log shows queries that take more than 'long_query_time' parameters (you can change this also) time to execute. And General query log contains all SQL statements received from clients.
But remember that these log will generate overhead so it is best to do this kind of tuning in test enviroment.

You can set slow query log on/off from my.cnf init file (or with starting server with these parameters) with slow_query_log[={0|1}] and optionally set the path and filename with slow_query_log_file=<path_and_log_name>  .
Mysql Servers older than 5.1.29 parameter name is 'log-slow-queries' for path and file name.

General query log is set with parameters on my.cnf (or with starting server with these parameters) general_log[={0|1}] to enable or disable, And optionally path and filename with  general_log_file=<path_and_log_name>.
Mysql Servers older than 5.1.29 parameter name is 'log' for path and file name.

After changes in my.cnf restart the MySQL server.

And  from the 5.1 version you can change these logs on the fly with these commands:

mysql> SET GLOBAL slow_query_log = ON;       (or set global slow_query_log=1; )
mysql> SET GLOBAL slow_query_log = OFF;      (or set global slow_query_log=0;)

mysql> SET GLOBAL general_log = ON;              (or set global general_log=1; )
mysql> SET GLOBAL general_log = OFF;            (or set global general_log=0;)

And logpaths can be seen from (check: log_output if this is file then check: general_log_file, slow_query_log_file) :
mysql> show variables;  



After you have set logs on you run normal load into database and check the logs. From slow log you see which SQL statements take's the most time to run. And then you start to tune those statements that are taking most time and are runned most often.

You can use mysqldumpslow command to summarize the queries that appear in the slow queries log. This will make it easier to find problematic statements (it will count same statements etc...).
mysqldumpslow [options] <path_and_slow_log_filename>

More info about mysqldumpslow:
http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html




2. Tuning single SQL clauses with explain and sql profiler:

When you want to tune single problematic SQL clause first start sql profiler (this should be working from 5.0.37 and newer versions MySQL Servers.):

#Turn sql profiler on:
mysql> SET @@profiling = 1;
#To see parameter value:
mysql> SELECT @@profiling;
#Turn sql profiler off:
mysql> SET @@profiling = 0;

#After you have set sql profiler on. Run the problematic sql with explain:
#For example (this is just an example not real slow query):
mysql> explain SELECT * FROM INFORMATION_SCHEMA.TABLES ;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | TABLES | ALL  | NULL          | NULL | NULL    | NULL | NULL | Open_full_table; Scanned all databases |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)
----------
'possible_keys' value tell's you what kind of keys/indexes query is trying to use. And 'Extra' tell's you how MySQL is trying to resolve the query (sort the data with where, index etc..). Explain tell's you also execution time but it is better to use profiler for time comparison when tuning queries.

#Check problematic sql profile number for more info (it is the last Query_ID in profiles) (this shows the last 15 queries default but you can change that value from profiling_history_size parameter. ) :
mysql> show profiles ;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration   | Query                                           |
+----------+------------+-------------------------------------------------+
|        1 | 0.00038300 | select @@profiling                              |
|        2 | 0.00084500 | show databases                                  |
|        3 | 0.00021600 | show profile for query2                         |
|        4 | 0.15603600 | SELECT * FROM INFORMATION_SCHEMA.TABLES         |
|        5 | 0.00068200 | explain SELECT * FROM INFORMATION_SCHEMA.TABLES |
+----------+------------+-------------------------------------------------+
5 rows in set (0.00 sec)
#Check problematic sql profile (this shows you steps that are made when query is running and also time for each step.) (You can also give options for 'show profile' command (CPU, MEMORY,SWAP... ) read more from here: http://dev.mysql.com/doc/refman/5.1/en/show-profile.html ):
mysql> show profile for query 5 ;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000197 |
| Opening tables     | 0.000099 |
| System lock        | 0.000016 |
| Table lock         | 0.000050 |
| init               | 0.000052 |
| optimizing         | 0.000020 |
| statistics         | 0.000026 |
| preparing          | 0.000024 |
| executing          | 0.000038 |
| end                | 0.000022 |
| query end          | 0.000015 |
| freeing items      | 0.000038 |
| removing tmp table | 0.000020 |
| closing tables     | 0.000034 |
| logging slow query | 0.000015 |
| cleaning up        | 0.000016 |
+--------------------+----------+
16 rows in set (0.00 sec)

#Now that you have better info what the query is doing and what is the most time consuming step (is it using correct index? Or can you change the query itself. etc...). You need to tune it and then run explain/profile steps again to see if you get improment for the query (compare first and new profile and explain plan).

#Usually making correct index is easiest solution for basic query problems. So you create new index and check if query is starting to use it and if it is doing improment for performance. (Sometimes you need to drop old indexes and/or use optimizer hint for queries to get them use correct indexes. More about index hints: http://dev.mysql.com/doc/refman/4.1/en/index-hints.html ):

mysql> create index...

mysql> explain select ...

mysql> show profiles ;

mysql> show profile for query 6 ;
.
.
.


Remember also that if you are tuning 'group by' clauses there is certain limits with those ( read more here: http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization.html ). And usually big index which contains all or most of the columns of query are more efficient (but also takes more time to update). And remember to drop all unnecessary indexes.