MySQL Optimization

Below will list 250 variables related to table locks, buffers and various other mysql parameters in general. This should be the entry point to see the health of MySQL on a system.

 mysql> show status;

Example of show status, below will give the count of waiting locks for a table.

 mysql> show status like 'Table_locks_waited';
 +--------------------+-------+
 | Variable_name      | Value |
 +--------------------+-------+
 | Table_locks_waited | 1     |
 +--------------------+-------+
 1 row in set (0.00 sec)

PROCEDURE_ANALYSE

PROCEDURE_ANALYSE, Get field types and field lengths correct which will improve performance of MySQL Indexes and Caches. Note that we should do this on a very mature database with actual values then the test values to gain benefit from this.

mysql> select user from vicidial_users PROCEDURE ANALYSE(1,1)G;
*************************** 1. row ***************************
Field_name: wikidb.vicidial_users.user
Min_value: 101
Max_value: U3004
Min_length: 2
Max_length: 8
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 7.4016
Std: NULL
Optimal_fieldtype: CHAR(8) NOT NULL
1 row in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

MySQL Log Slow Queries

Log Slow Queries which are not indexed, queries taking more then 1 second are logged by default :

In my.cnf we need the following lines

 log_slow_queries = /var/log/mysql/mysql-slow.log
 long_query_time = 1
 log-queries-not-using-indexes

To see the logged slow queries :

 cat /var/log/mysql/mysql-slow.log | more

MySQL QUERY PLAN

To see analysis of query execution use EXPLAIN :

 mysql> EXPLAIN SELECT user FROM vicidial_users;
 +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+
 | id | select_type | table          | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
 +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+
 |  1 | SIMPLE      | vicidial_users | index | NULL          | user | 22      | NULL |    2 | Using index |
 +----+-------------+----------------+-------+---------------+------+---------+------+------+-------------+
 1 row in set (0.00 sec)

Use EXPLAIN EXTENDED for more verbose output. The parameter ‘extended’ will get any additional information MySQL has on the query. The ‘G’ at the end of the query will format the output into a more readable form.

 mysql> EXPLAIN EXTENDED select user from vicidial_users WHERE user NOT IN ()G;
 +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+
 | id | select_type | table          | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+
 |  1 | SIMPLE      | vicidial_users | index | user          | user | 22      | NULL |    2 | Using where; Using index |
 +----+-------------+----------------+-------+---------------+------+---------+------+------+--------------------------+
 1 row in set, 1 warning (0.00 sec)

Optimization Hints

MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.
It should noted that using the hints incorrectly cause our queries to perform even worse, so be careful that the hint makes sense before using them.
Use EXPLAIN with a sample query with hint before making a change to understand how the query will be processed.

It is also a good idea to enclose the hints within a SQL comment, for example

 SELECT /*! SQL_NO_CACHE */ columns FROM table.

This can help us to make our application a bit more portable.