O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

Scalability of various anti-join techniques (no index)
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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required