Conclusion
In this chapter, we looked at some more advanced SQL tuning scenarios.
We first looked at simple subqueries using the IN
and EXISTS
operators. As with joins and simple
single-table queries, the most important factor in improving subquery
performance is to create indexes that allow the subqueries to execute
quickly. We also saw that when an appropriate index is not available,
rewriting the subquery as a join can significantly improve
performance.
The anti-join is a type of SQL operation that returns all rows
from a table that do not have a matching row in a second table. These
can be performed using NOT IN
,
NOT EXISTS
, or LEFT JOIN
operations. As with other
subqueries, creating an index to support the subquery is the most
important optimization. If no index exists to support the anti-join,
then a NOT IN
subquery will be more
efficient than a NOT EXISTS
or a
LEFT JOIN
.
We can also place subqueries in the FROM
clause—these are sometimes referred to
as inline views, unnamed
views, or derived tables. Generally
speaking, we should avoid this practice because the resulting
“derived” tables will have no indexes and will perform poorly if they
are joined to another table or if there are associated selection
criteria in the WHERE
clause. Named
views are a much better option, since MySQL can “merge” the view
definition into the calling query, which will allow the use of indexes
if appropriate. However, views created with the TEMPTABLE
option, or views that cannot take advantage of ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.