Other Indexes

Besides choosing a primary key, it is also important to create the required, and correct, secondary indexes. Most database developers use the attributes that are components of joins, or on which a query filters information (using a WHERE clause), for this purpose. In the example

mysql> SELECT * FROM employee WHERE last = 'Mueller';

it would make sense to index on the field named last.

Whether or not the MySQL optimizer actually uses an index can be determined with the EXPLAIN command (Listing 9). Besides checking whether or not an index is used, you will also want to measure the run time of the command multiple times to make sure the action really does have a positive effect. It can happen that the selected index causes poorer performance.

mysql> EXPLAIN SELECT * FROM employee WHERE last = 'Mueller';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | employee | ALL  | NULL          | NULL | NULL    | NULL |  987 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
mysql> ALTER TABLE employee ADD INDEX (last);
mysql> EXPLAIN SELECT * FROM employee WHERE last = 'Mueller';
+----+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | employee | ref  | last          | last | 67      | const |    1 | Using index condition |
+----+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+

Slow Query Log

To record slow queries systematically, MySQL has the Slow Query Log. You can enable and configure the log dynamically as of MySQL 5.1:

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL long_query_time = 0.200;

You can also query where the Slow Query Log resides on disk:

mysql> SHOW GLOBAL VARIABLES   LIKE 'slow%file';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slow_query_log_file | slow.log |
+---------------------+----------+

If the path is not specified, the file will reside below the $datadir in /var/lib/mysql.

Level 5: The Architecture

The fifth level at which you have tuning options is the architecture. It typically provides the greatest leverage for improvements, but you need to apply it right from the outset of the development process. If you make the wrong decision here, it is difficult to correct and will typically involve substantial overhead.

Important values are the number of users and the data volume you will have in the final expansion stage. The larger the anticipated values, the more you need to think about the architecture. Some points that are continually debated include the following:

• Database clusters give better performance. This assumption tends to be false. Database clusters are typically a high-availability solution and not a solution for tackling performance problems. Response times for read access remain the same or could even be longer. The read performance will typically improve with most MySQL cluster solutions – as long as you are prepared to accept certain restrictions. Writing will scale badly or not at all.
• Parallelization can drastically improve an application's throughput. MySQL can easily handle dozens of queries at the same time, but this means having the right kind of application. MySQL cannot autonomously parallelize queries.
• For applications that need to scale massively and will have a large percentage of read accesses, it makes sense to open separate read and write connections to the database in the application. This approach will allow you to redirect read queries to a MySQL read cluster later on. Caution is advisable with systems that boast of automatic read-write splitting. Developing reliable intelligence that automatically makes the right decision is difficult. How is an external developer going to do this if your own developers find it impossible? Additionally, these automatic systems typically only work for single-query transactions.
• The biggest problem in scaling is writing. Difficulties in reading can be tackled with a large amount of RAM and a large enough number of read slaves. If write throughput brings the machine to its knees, however, the only thing that helps is a fast I/O system (e.g., an SSD). If this does not help, your final resort is sharding – that is, distributing the data across multiple database back ends. Each back end has its own I/O system, which means that write access can scale infinitely – in theory. This also has a significant influence on the application, however. The application needs to know where the data resides to be able to write it to the correct back end, and this requirement often means low-level changes to the application code.