MySQL Stored Procedure Programming

Tuning Subqueries in the FROM Clause

It is possible to include subqueries within the FROM clause of a SQL statement. Such subqueries are sometimes called unnamed views , derived tables , or inline views .

For instance, consider the query in Example 21-10, which retrieves a list of employees and department details for employees older than 55 years.

Figure 21-7. Scalability of various anti-join techniques (no index)

Example 21-10. Example SQL suitable for rewrite with an inline view

SELECT departments.department_name,employee_id,surname,firstname
  FROM departments
       JOIN employees
       USING (department_id)
 WHERE employees.date_of_birth<date_sub(curdate(  ),interval 55 year)

Short Explain

1    SIMPLE select(range) on employees using i_employee_dob
          Using where
1    SIMPLE select(eq_ref) on departments using PRIMARY
          Using where

This query is well optimized—an index on date of birth finds the customers, and the primary key index is used to find the department name on the departments table. However, we could write this query using inline views in the FROM clause, as shown in Example 21-11.

Example 21-11. SQL rewritten with an inline view

SELECT departments.department_name,employee_id,surname,firstname
  FROM (SELECT * FROM departments ) departments
        JOIN (SELECT * FROM employees) employees
        USING (department_id)
 WHERE employees.date_of_birth<DATE_SUB(curdate( ), INTERVAL 55 YEAR) Explain plan 1 PRIMARY ...