MySQL Query Optimization Tool

Background

This tool tests the sample mySQL employee database, with over 2 million records against the following query:

SELECT employees.emp_no, first_name, last_name, title, titles.from_date AS titles_from_date, titles.to_date AS titles_to_date, salary, salaries.from_date AS salary_from_date, salaries.to_date AS salaries_to_date
FROM employees
INNER JOIN titles ON employees.emp_no = titles.emp_no
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
LIMIT ?

The SQL query is then preceded and proceeded by:
SET PROFILING = 1;
SET PROFILING = 0;
Setting profiling enables mySQL's tracking of the performance tracking of the query. mySQL will then do careful bookkeeping and track the performance of the query. This can illuminate where slow queries are slowing down.

EXPLAIN Keyword

Any valid SELECT statement can be “explained” by mySQL. The EXPLAIN keyword will map out mySQL's plan for how it will execute the statement. Every EXPLAIN command will contain the following data about mySQL's plan:

  1. id: sequential number for the query
  2. select_type: the type of query. Of interest is whether the query is a standalone (SIMPLE) query, a type of subquery, or UNION query.
  3. table: the table the query is referencing.
  4. type: the type of join mySQL is using. The most important values are:
    • eq_ref: the join is performed solely on primary keys. This is the best possible join.
    • ref: the join is performed using a non-unique index. This is an excellent join type when fields are not unique.
    • index: the entire index is scanned to perform the join.
    • all: the entire table is scanned to perform the join. This is the worst case scenario!
  5. possible_keys: the indexes mySQL may be able to use when performing the join. If this value is NULL, no possible indexes are found.
  6. key: the actual key used when performing the join. Note this may not be one of the possible keys.
  7. key_len: the size of the key being used in the join.
  8. ref: the keys actually used in the join.
  9. rows: the number of rows involved in the join.
  10. extra: additional information about the join.

Deeper explanation and all the possible values for EXPLAIN output can be found in the mySQL documentation.

EXPLAIN Example

Table 1 and Table 2 explain the query using indexed and non-indexed data.

Table 1: EXPLAIN Example on Non-Indexed Data
idselect_typetabletypepossible_keyskeykey_lenrefrowsextra
1SIMPLEemployeesALLNULLNULLNULLNULL300024NULL
1SIMPLEtitlesALLNULLNULLNULLNULL443308Using where; Using join buffer (Block Nested Loop)
1SIMPLEsalariesALLNULLNULLNULLNULL2844047Using where; Using join buffer (Block Nested Loop)
Table 2: EXPLAIN Example on Indexed Data
idselect_typetabletypepossible_keyskeykey_lenrefrowsextra
1SIMPLEemployeesALLPRIMARYNULLNULLNULL300024NULL
1SIMPLEtitlesrefPRIMARY,emp_noemp_no4employees.employees.emp_no1NULL
1SIMPLEsalariesrefPRIMARY,emp_noemp_no4employees.employees.emp_no10NULL

Observe the type column. The non indexed joins are both ALL. This means that mySQL is going to scan all rows in order to perform the join. As previously mentioned, this is the worst case scenario. If this appears on a join, strongly consider creating appropriate indexes or rewriting the query to take advantage of other indexes.

Warning

Empirical tests indicate the two joins take a very long time, even for a small number of results. Be prudent with choosing numbers; work your way up slowly.

mySQL Query Performance Tester