QUERY:
SELECT
(
SELECT COUNT(cleanpoi.ID) FROM cleanpoi
WHERE cleanpoi.EstablishmentID=parent.ID
) AS POIs,
(
SELECT COUNT(ID) FROM cleanamenitymappings WHERE CleanPOIID IN
(
SELECT cleanpoi.ID FROM cleanpoi
WHERE cleanpoi.EstablishmentID=parent.ID
)
) AS Amenities,
(
SELECT COUNT(ID) FROM cleanamenityvalues WHERE CleanPOIID IN
(
SELECT cleanpoi.ID FROM cleanpoi
WHERE cleanpoi.EstablishmentID=parent.ID
)
) AS AmenityValues
FROM establishment parent
WHERE parent.ID=3
EXPLAIN RESULT:
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ -------------------- --------------- ----------------------------------------- --------------- ------- ------ ------ --------------------------
1 PRIMARY parent const PRIMARY PRIMARY 4 const 1 Using index
5 DEPENDENT SUBQUERY cleanamenityvalues ALL (NULL) (NULL) (NULL) (NULL) 31778 Using where
6 DEPENDENT SUBQUERY cleanpoi unique_subquery PRIMARY,EstablishmentID_2,EstablishmentID PRIMARY 4 func 1 Using where
3 DEPENDENT SUBQUERY cleanamenitymappings index (NULL) CleanPOIID 4 (NULL) 673591 Using where; Using index
4 DEPENDENT SUBQUERY cleanpoi unique_subquery PRIMARY,EstablishmentID_2,EstablishmentID PRIMARY 4 func 1 Using where
2 DEPENDENT SUBQUERY cleanpoi ref EstablishmentID_2,EstablishmentID EstablishmentID 4 const 181
Any advice with this query? Your answers are really appreciated!
Thanks for all the answers but this solved my problem.
SELECT
(
SELECT COUNT(cleanpoi.ID) FROM cleanpoi
WHERE cleanpoi.EstablishmentID=parent.ID
) AS POIs,
(
SELECT COUNT(a.ID)
FROM cleanamenitymappings a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
WHERE b.EstablishmentID=parent.ID
) AS Amenities,
(
SELECT COUNT(a.ID)
FROM cleanamenityvalues a LEFT JOIN cleanpoi b ON a.CleanPOIID=b.ID
WHERE b.EstablishmentID=parent.ID
) AS AmenityValues
FROM establishment parent
WHERE parent.ID=3