I am not as familiar with the EXPLAIN feature as I should be, can anyone help me with this slow query? Sorry if the table is a bit scattered:
here is my query:
EXPLAIN SELECT m . / number of leases that show present COUNT(DISTINCT l.ID) AS Leases,/
FROM _v_properties_master_list m
LEFT JOIN gl_leases l ON m.ID = l.Units_ID
AND / the unit/house/etc is leased AS OF THE INTENDED MOVE-IN DAY OF THE SEARCH - we filter this out outside of the left join .. */ '2013-05-03'
BETWEEN l.LeaseStartDate
AND IF( l.LeaseTerminationDate, l.LeaseTerminationDate, IF( l.LeaseEndDate, l.LeaseEndDate, '2199-12-31' ) )
WHERE m.Active =1
AND
TYPE IN (
'APT'
)
AND Bedrooms =1
AND Bathrooms =1
AND PropertyCity
IN (
'San Marcos'
)
AND (
1
)
AND m.ID
IN ( 105, 848 )
GROUP BY m.ID
ORDER BY PropertyName ASC
and here is the result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 777 Using where; Using temporary; Using filesort
1 PRIMARY l ref LeaseStartDate,Units_ID Units_ID 4 m.ID 20
2 DERIVED cl ALL PRIMARY NULL NULL NULL 423 Using temporary; Using filesort
2 DERIVED p ref Clients_ID Clients_ID 4 cpm151_sanmarcos.cl.ID 1 Using where
2 DERIVED u ref Clients_ID Clients_ID 4 cpm151_sanmarcos.p.ID 2
2 DERIVED a ref LeaseStartDate,Units_ID Units_ID 4 cpm151_sanmarcos.u.ID 20
2 DERIVED flprimary ref Type,Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
2 DERIVED flc eq_ref PRIMARY PRIMARY 4 cpm151_sanmarcos.flprimary.Contacts_ID 1
2 DERIVED flall ref Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
2 DERIVED a ref LeaseStartDate,Units_ID Units_ID 4 cpm151_sanmarcos.u.ID 20
2 DERIVED clprimary ref Type,Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
2 DERIVED clc eq_ref PRIMARY PRIMARY 4 cpm151_sanmarcos.clprimary.Contacts_ID 1
2 DERIVED clall ref Leases_ID Leases_ID 4 cpm151_sanmarcos.a.ID 1
now, _v_properties_master_list is a fairly complex and I will post that separately if needed. Thanks!
