So I have a database of well a million rows for UK postcodes along with their easting/northing values and lat/lng values. The postcode is the primary key as well as there being indexes on both conbinations of co-ordinates. The DISTANCE() function is a user defined function I have created to work out a radius from the given postcode. The below SQL statement works without issue and in a reasonable amount of time. The problem I have is when I try and order by the column "distance". Is there any way of making the query faster to allow for me to order this way? SELECT search.*, DISTANCE(location.easting, location.northing, search.easting, search.northing) AS distance FROM postcode AS search INNER JOIN postcode AS location ON location.postcode = 'example' WHERE (search.easting BETWEEN (location.easting - 16090) AND (location.easting + 16090)) AND (search.northing BETWEEN (location.northing - 16090) AND (location.northing + 16090)) HAVING distance <= 16090 LIMIT 2000 Code (markup):
I should point out that I have tried both InnoDB and MyISAM with not much difference between the two. The records in the table will hardly ever change.
Hi. I'm ready to help you, but can you pls provide me DB structure with some small part of data? I think i can optimize this query, if not - you can easy use partitioning for your table.