Page 1 of 1

Selecting closet numbers in a MySQL Database

Posted: Sun Feb 01, 2009 7:13 pm
by Mr Tech
I can't seem to get my head around this problem...

Let's say I have a postcode search where you can enter your postcode and search a MySQL Database. I want to be able to pull out the 6 closet postcodes to the postcode searched for...

What query would I have to run for this? I was thinking something like the below query but I don't think it would work because it would pull out practically any number not just the closet:

Code: Select all

SELECT * FROM postcodes WHERE postcode='4213' or postcode<='4213' or postcode>='4213' LIMIT 10
I can't really use ORDER BY postcode asc because i need to search numbers less than the postcode and numbers greater than the postcode.

Anyone have any ideas for me?

Re: Selecting closet numbers in a MySQL Database

Posted: Sun Feb 01, 2009 7:33 pm
by Eran
order by the distance from the zipcode:

Code: Select all

SELECT *,ABS(zipcode - 4213) AS distance FROM postcodes ORDER BY distance LIMIT 10

Re: Selecting closet numbers in a MySQL Database

Posted: Mon Feb 02, 2009 5:47 am
by VladSun
viewtopic.php?f=2&t=92314

If it's enough for your needs I think it will be faster than using the ABS() function, because indexies are used for ORDER BY.

Re: Selecting closet numbers in a MySQL Database

Posted: Mon Feb 02, 2009 6:04 am
by Eran
You can combine those, I didn't want to mention it because it gets ugly:

Code: Select all

SELECT *,ABS(zipcode - 4213) AS distance 
FROM (
    (SELECT * FROM postcodes WHERE zipcode >= 4213 ORDER BY zipcode LIMIT 11)
    UNION ALL
    (SELECT * FROM postcodes WHERE zipcode < 4213 ORDER BY zipcode DESC LIMIT 10)
) AS n ORDER BY distance LIMIT 10
 
However for a small table (a zipcode table for example - ~60k rows) the distance is very fast if the zipcode field is indexed.
I compared the approaches here - http://www.techfounder.net/2009/02/02/s ... -in-mysql/

Re: Selecting closet numbers in a MySQL Database

Posted: Mon Feb 02, 2009 6:30 am
by VladSun
Well done. Nice benchmarks :)

Re: Selecting closet numbers in a MySQL Database

Posted: Mon Feb 02, 2009 5:32 pm
by Mr Tech
That's great! Thank you so much :)