title: Results within radius - Optimising slow MySQL query
body: <pre><code>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 &gt; 0
          GROUP BY CONCAT(property.paon, ', ', property.street)
  
          HAVING distance &lt; 1.4 
          ORDER BY property.lastTransferDate DESC
          LIMIT 10
  </code></pre>
  
  <p>table property has 22 million rows, table epc has 14 million rows</p>
  
  <p>Without the GROUP BY and ORDER BY, it runs fast.</p>
  
  <p>Property table often has the same property multiple times, but I need to select the one with the most current lastTransferDate.</p>
  
  <p>If there is a better approach I'm open to it</p>
  
  <p>Here is the explain of query:
  <a href="https://i.stack.imgur.com/OtG5N.png" rel="nofollow noreferrer">Query-Explain-Image</a></p>