The query below is slow because both the lots table and the deficiency_table
table have thousands of records. Can anyone tell me how to do the second
subselect (lot_count) by some method of a join instead of a sub - subselect
OR any other method I can use to optimize this query to make it faster?
The objective of the query is: Tell me for each project, the total number
of deficiencies in the project, and the total number of lots with 1 or more
deficiencies in the project.
SELECT project_id, marketing_name,
(SELECT count(lots.lot_id) AS lot_count
FROM deficiency_table AS dt, lots
WHERE dt.lot_id = lots.lot_id
AND lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
) AS def_count,
(SELECT count(lots.lot_id) AS lot_counter
FROM lots
WHERE lots.division_id = proj.division_id
AND lots.project_id = proj.project_id
AND EXISTS (SELECT 1 FROM deficiency_table AS dt WHERE
dt.lot_id =
lots.lot_id)
) AS lot_count
FROM projects AS proj
WHERE proj.division_id = '#variables.local_division_id#'
AND NOT EXISTS (SELECT 1 FROM menu_group_projects WHERE menu_code = 'WA'
AND division_id = proj.division_id AND project_id = proj.project_id AND
status = 'I')
ORDER BY proj.project_id
Thanks in advance
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
