SQL语句如下:
SELECT h.*,e.AreaId,e.CityId,e.Subway,e.SchoolDistrict,e.Title AS EstateName,e.Image AS CoverImg
FROM HouseBasicInfo h
INNER JOIN Estate e ON h.EstateId=e.Id
WHERE h.EstateId IN (
SELECT EstateID FROM HouseDistribution WHERE DepartmentID = 1991 AND PropertyType = 11774
)
AND h.Status=58 AND h.IsDelete=0 AND h.HouseType=1 AND e.CityId IN (20)
GROUP BY h.CreateTime
ORDER BY h.CreateTime
DESC LIMIT 0,20;
1、[quote=“Hacker_5icAWmjG, post:1, topic:92813”]
IN (
SELECT EstateID FROM HouseDistribution WHERE DepartmentID = 1991 AND PropertyType = 11774
)
[/quote]
是不是这个条件返回的记录比较多,建议把IN的写法改为EXISTS的方式,性能会有提升;
2、ORDER BY h.CreateTime DESC,这个是降序排序,如果你建索引是建的升序,可能利用索引GROUP BY之后,需要重新进行排序操作。