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

Chapter 21. Advanced SQL Tuning

In the last chapter, we emphasized that high-performance stored programs require optimized SQL statements. We then reviewed the basic elements of SQL tuning — namely, how to optimize single-table accesses and simple joins. These operations form the building blocks for more complex SQL operations.

In this chapter, we will look at optimizing such SQL operations as:

  • Subqueries using the IN and EXISTS operators

  • "Anti-joins" using NOT IN or NOT EXISTS

  • "Unamed" views in FROM clauses

  • Named or permanent views

  • DML statements (INSERT, UPDATE, and DELETE)

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

SELECT COUNT(*)
  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
             Rows=100459
             Key=                (Possible=                              )
             Ref=                 Extra=Using where
    ID=2     Table=employees      Select type=DEPENDENT SUBQUERY   Access type=ALL
             Rows=1889
             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 ...

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