SQL Tuning Statements and Practices
MySQL provides several statements and utilities that assist with tuning SQL, and you need to be familiar with these resources. The statements and utilities are described in the following sections.
EXPLAIN Statement
The most important SQL tuning statement in the MySQL language
is EXPLAIN. EXPLAIN exposes the execution plan that
the optimizer will use to resolve a particular SQL statement.
Without EXPLAIN, you are doomed
to trial-and-error tuning.
EXPLAIN has a simple
syntax:
EXPLAIN sql_text;EXPLAIN returns a result
set consisting of at least one row for each table referenced in the
SQL. Additional rows might be returned to indicate how subqueries or
derived tables are used in the query. Example 19-3 is a simple
demonstration of an explain plan for a two-table join (we used the
\G option to print the output
with each column on a separate line).
Example 19-3. Example of EXPLAIN output
mysql> EXPLAIN SELECT customer_name
-> FROM employees join customers
-> ON(customers.sales_rep_id=employees.employee_id)
-> WHERE employees.surname='GRIGSBY'
-> AND employees.firstname='RAY' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees1
type: ref
possible_keys: PRIMARY,i_employees_name3
key: i_employees_name4
key_len: 80
ref: const,const
rows: 15
Extra: Using where; Using index6
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customers2 type: ref possible_keys: ...