Tuning Subqueries

A subquery is a SQL statement that is embedded within the WHERE clause of another statement. For instance, Example 21-1 uses a subquery to determine the number of customers who are also employees.

Example 21-1. SELECT statement with a subquery
  FROM customers
 WHERE (contact_surname, contact_firstname,date_of_birth)
    IN (select surname,firstname,date_of_birth
          FROM employees)

We can identify the subquery through the DEPENDENT SUBQUERY tag in the Select type column of the EXPLAIN statement output, as shown here:

    Explain plan

    ID=1     Table=customers     Select type=PRIMARY              Access type=ALL
             Key=                (Possible=                              )
             Ref=                 Extra=Using where
    ID=2     Table=employees      Select type=DEPENDENT SUBQUERY   Access type=ALL
             Key=                (Possible=                              )
             Ref=                 Extra=Using where

The same query can also be rewritten as an EXISTS subquery, as in Example 21-2.

Example 21-2. SELECT statement with an EXISTS subquery
SELECT count(*)
  FROM customers
                 FROM employees
                where surname=customers.contact_surname
                  AND firstname=customers.contact_firstname
                  AND date_of_birth=customers.date_of_birth)

Short Explain
1    PRIMARY select(ALL) on customers using no key
          Using where
2    DEPENDENT SUBQUERY select(ALL) on employees using no key

Note that the EXPLAIN output for the EXISTS subquery is identical to that of the IN subquery. This is because MySQL rewrites IN-based subqueries as EXISTS-based syntax before execution. The performance of subqueries will, ...

Get MySQL Stored Procedure Programming now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.