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
"Unamed" views in
Named or permanent views
DML statements (
A subquery is a SQL statement that
is embedded within the
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
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 SUBQUERYAccess 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 ...