Database Reference
In-Depth Information
—writing a bunch of subqueries independently and then assembling them mindlessly
to get the final result. You have to treat each query holistically. How you piece together
data from different views and tables is every bit as important as how you go about
retrieving the data in the first place.
The unnecessary use of subqueries, as shown in
Example 9-12
, is a common symptom
of piecemeal thinking.
Example 9-12. Overusing subqueries
SELECT
tract_id
,
(
SELECT
COUNT
(
*
)
FROM
census
.
facts
As
F
WHERE
F
.
tract_id
=
T
.
tract_id
)
As
num_facts
,
(
SELECT
COUNT
(
*
)
FROM
census
.
lu_fact_types
As
Y
WHERE
Y
.
fact_type_id
IN
(
SELECT
fact_type_id
FROM
census
.
facts
F
WHERE
F
.
tract_id
=
T
.
tract_id
)
)
As
num_fact_types
FROM
census
.
lu_tracts
As
T
;
Example 9-12
can be more efficiently written as shown in
Example 9-13
. This query,
consolidating selects and using a join, is not only shorter than the prior one, but faster.
If you have a larger dataset or weaker hardware, the difference could be even more
pronounced.
Example 9-13. Overused subqueries simplified
SELECT
T
.
tract_id
,
COUNT
(
f
.
fact_type_id
)
As
num_facts
,
COUNT
(
DISTINCT
fact_type_id
)
As
num_fact_types
FROM
census
.
lu_tracts
As
T
LEFT
JOIN
census
.
facts
As
F
ON
T
.
tract_id
=
F
.
tract_id
GROUP
BY
T
.
tract_id
;
Figure 9-4
shows the graphical plan for
Example 9-12
(we'll save you the eyesore of
seeing the gnarled output of the text
EXPLAIN
), while
Figure 9-5
shows the tabular output