query:
select ( select count(cleanpoi.id) cleanpoi cleanpoi.establishmentid=parent.id ) pois, ( select count(id) cleanamenitymappings cleanpoiid in ( select cleanpoi.id cleanpoi cleanpoi.establishmentid=parent.id ) ) amenities, ( select count(id) cleanamenityvalues cleanpoiid in ( select cleanpoi.id cleanpoi cleanpoi.establishmentid=parent.id ) ) amenityvalues establishment parent parent.id=3 explain result:
id select_type table type possible_keys key key_len ref rows ------ ------------------ -------------------- --------------- ----------------------------------------- --------------- ------- ------ ------ -------------------------- 1 primary parent const primary primary 4 const 1 using index 5 dependent subquery cleanamenityvalues (null) (null) (null) (null) 31778 using 6 dependent subquery cleanpoi unique_subquery primary,establishmentid_2,establishmentid primary 4 func 1 using 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 2 dependent subquery cleanpoi ref establishmentid_2,establishmentid establishmentid 4 const 181 any advice query? answers appreciated!
your query scan through 700k rows, need read disk. guessing on poor disk performance (bandwidth and/or seek time).
if run query often, consider creating materialized view, table holding answer entire query , update using triggers when underlying data changes.
Comments
Post a Comment