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
INandEXISTSoperators"Anti-joins" using
NOT INorNOT EXISTS"Unamed" views in
FROMclausesNamed or permanent views
DML statements (
INSERT,UPDATE, andDELETE)
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 whereThe same query can also be rewritten as an EXISTS subquery, as in Example 21-2.
Example 21-2. SELECT statement with an EXISTS ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access