半径范围内的结果-优化慢速MySQL查询 [英] Results within radius - Optimising slow MySQL query

问题描述

SELECT property.paon, property.saon, property.street, property.postcode, property.lastSalePrice, property.lastTransferDate,
 epc.ADDRESS1, epc.POSTCODE, epc.TOTAL_FLOOR_AREA,  
        (
            3959 * acos (
                cos (radians(54.6921))
                * cos(radians(property.latitude))
                * cos(radians(property.longitude) - radians(-1.2175))
                + sin(radians(54.6921))
                * sin(radians(property.latitude))
            )
        ) AS distance 
        FROM property 
        RIGHT JOIN epc ON property.postcode = epc.POSTCODE AND CONCAT(property.paon, ', ', property.street) = epc.ADDRESS1 
        WHERE property.paon IS NOT NULL AND epc.TOTAL_FLOOR_AREA > 0
        GROUP BY CONCAT(property.paon, ', ', property.street)

        HAVING distance < 1.4 
        ORDER BY property.lastTransferDate DESC
        LIMIT 10

表属性具有2200万行，表epc具有1400万行

table property has 22 million rows, table epc has 14 million rows

没有GROUP BY和ORDER BY，它运行很快.

Without the GROUP BY and ORDER BY, it runs fast.

属性表通常多次具有相同的属性，但我需要选择一个具有最新lastTransferDate的属性.

Property table often has the same property multiple times, but I need to select the one with the most current lastTransferDate.

如果有更好的方法，我愿意接受它

If there is a better approach I'm open to it

这是查询的解释: 查询解释图像

推荐答案

您可以做一些事情:

• 创建一个新列，因此您无需在GROUP BY和JOIN中使用CONCAT CONCAT(property.paon, ', ', property.street)(这将大大加快速度！)
• 正如JackHacks所说，您需要在正确的位置创建索引. (属性邮政编码和新创建的列，以及epc邮政编码和地址)
• 用epc.TOTAL_FLOOR_AREA > 0删除HAVING并将其添加到WHERE

• Create a new column so you don't need to use CONCAT CONCAT(property.paon, ', ', property.street) in the GROUP BY and the JOIN (this will speed it up a lot!)
• As JackHacks says you need to create indexes at the right spot. (property postcode and the newly created column, and epc postcode and address)
• Remove the HAVING with epc.TOTAL_FLOOR_AREA > 0 and add it to the WHERE

如果您需要更多帮助，请与我们分享您的查询解释.

If you need more help, share en EXPLAIN of your query with us.

这篇关于半径范围内的结果-优化慢速MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！