After connection management, the next component involved is the query cache. This stores the result sets of read (SELECT) statements that have executed in the recent past. Before further processing, if there is a cache hit, results are returned from the cache itself.

MySQL parses queries to create an internal structure called the parse tree, and then performs query planning and optimization on it. These include rewrites, choosing indexes, choosing the order in which it will read tables, and so on. A pass hints to the optimizer through special keywords in the query.

The optimizer component is also responsible for the EXPLAIN feature, which helps developers in figuring out how a query is performing. Using EXPLAIN is as simple as prefixing the EXPLAIN keyword to the query:

EXPLAIN SELECT * FROM hotel_reservations LIMIT 10;

It produces the following output:

********************** 1. row **********************
 id: 1
 select_type: SIMPLE
 table: hotel_reservations
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 2
 Extra:
 1 row in set (0.00 sec)

There is a wealth of information in the output, the most important of which is described here:

• id: An identifier for each SELECT within the query. This is relevant for nested queries.
• select_type: The type of SELECT query. Possible values are as follows:
  • Simple: The query is a simple SELECT query without any subqueries or UNION
  • Primary: The SELECT is in the outermost query in a JOIN
  • Derived: The SELECT is part of a subquery within a FROM clause
  • Subquery: It is the first SELECT in a subquery
  • Dependent subquery: This is a subquery that is dependent upon on outer query
  • Uncachable subquery: It is a subquery that is not cacheable (there are certain conditions for a query to be cacheable)
  • Union: The SELECT is the second or later statement of a UNION
  • Dependent union: The second or later SELECT of a UNION is dependent on an outer query
  • Union result: The SELECT is a result of a UNION
• type: This is one of the most important fields and describes how MySQL plans to join the tables used. This can be used to infer missing indexes and/or whether the query needs redesign. Important values are as follows:
  • const: The table has only one matching row, which is indexed. This enables the fastest join since the value has to be read once, and can effectively be treated as a constant when joining other tables.
  • eq_ref: All parts of an index are used by the join, and the index is a PRIMARY KEY or UNIQUE NOT NULL. This is the next best possible execution plan for a query.
  • ref: All of the matching rows of an indexed column are read for each combination of rows from the previous table. This type of join appears for indexed columns compared using the = or <=> operators.
  • fulltext: The query uses a FULLTEXT search index—this is used for information retrieval in TEXT fields.
  • index_merge: The join uses a list of indexes to produce the result set. The key column of the EXPLAIN output will contain the keys used.
  • unique_subquery: An IN subquery returns only one result from the table and makes use of the primary key.
  • index_subquery: The same as unique_subquery, but returns more than one result row.
  • range: An index is used to find matching rows in a specific range, typically when the key column is compared to a constant using operators, such as BETWEEN, IN, >, and >=.
  • index: The entire index is scanned.
  • all: The entire table is scanned. This is the worst join type and indicates a lack of appropriate indexes.
  • possible_keys: Shows the keys that can be (but may not be used in the actual execution) used by MySQL to find rows from the table. If the column is NULL, it indicates that no relevant indexes could be found.
  • key: Indicates the actual index used by MySQL. The optimizer always looks for an optimal key that can be used for the query, and sometimes it may figure out some other keys that are not listed in possible_key but are more optimal.
  • rows: Lists the number of rows that were examined to execute the query. This is another important column worth focusing on for optimizing queries, especially for queries that use JOIN and subqueries.
  • Extra: Contains additional information regarding the query execution plan. Values such as using temporary and using filesort in this column may indicate a troublesome query. For a complete list of possible values and their meanings, refer to the MySQL documentation (https://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information).

To demonstrate how EXPLAIN can be used to help with debugging, consider the following query:

EXPLAIN SELECT * FROM
hotel_reservations r
INNER JOIN orders o ON r.reservationNumber = o.orderNumber
INNER JOIN customers c on c.id = o.customerId
WHERE o.orderNumber = PQR1111

This will produce the following output:

********************** 1. row ********************
id: 1
select_type: SIMPLE
table: c
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 70
Extra:
********************** 2. row ********************
id: 1
select_type: SIMPLE
table: o
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 210
Extra: Using join buffer
********************** 3. row ********************
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3000
Extra: Using where; Using join buffer
3 rows in set (0.00 sec)

The preceding results indicate a bad query. The join type is shown as ALL (the worst) for all tables—this means that MySQL was unable to identify any indexes to help with join. The rows column shows that the DB had to scan all of the records of each table for query. This means that for executing the query, it will read 70 × 210 × 3,000 = 44,100,000 rows to find the matching results. This is very bad performance and will degrade as the data size grows. An obvious way to help this query is to add indices, like so:

ALTER TABLE customers
ADD PRIMARY KEY (id);
ALTER TABLE orders
ADD PRIMARY KEY (orderNumber),
ADD KEY (customerId);
ALTER TABLE hotel_reservations
ADD PRIMARY KEY (reservationNumber);

This will enable MySQL to utilize indices and avoid extended table reads.