Mysql – Find the closest bar, when searching for a drink

MySQLperformancequery-performancespatial

Problem: Find the closest bar that serves the drink(s) I'm looking for.

Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW

The API Request that interacts with this problem will have these parameters

query, String, ideally the drink name 
lng, double, the starting longitude
lat, double, the starting latitude
range, integer, max distance in meters (with a default value)

The query parameter may select more than one drink (think about searching for "Vodka").

What would be a good strategy to write a SQL query with good performance?

I'm not very expert, but my idea is to

  • SELECT bars in the range
  • SELECT from drink__bars where bar_id is in the previous select result
  • JOIN the drinks table to get drinks data

How do I set the order based on the distance?

Any suggestion is appreciated!

Best Answer

set @x = 15;
set @y = 25;

select sqrt(pow(b.lat-@y,2) + pow(b.lng-@x,2)) as distance,  b.name, d.name
from `drinks` d
 join `drink__bars` db on d.id = db.drink_id
 join  `bars` b on b.id = db.bar_id
where d.`name` = 'Beer'
order by distance asc;

Full sqlfiddle, with some data. Use your preferred distance function to replace the plain one I used.

I would recommend against having Id on many-to-many relations. And prefer NOT NULL always, unless you have a very strong case to allow NULLs (you don't).

Related Question