Hi,
First to give some bacground: there is a table of firms with a primary key auth_id; a firm can have many locations (in separate table). There is also a table of keywords and a cross reference table linking firms with keywords. The problem is to find one location from each firm that matches the keyword and the location is nearest to a set point. The following query gives me a list of all locations of firms matching a keyword with calculated distances (squared):
SELECT l.id,l.auth_id, POW( l.x - 637340, 2 ) + POW( l.y - 486775, 2 ) AS distsq
FROM localization l
WHERE l.auth_id IN (
SELECT fkx.auth_id
FROM keywords k
JOIN firms_keywords_xref fkx ON k.id = fkx.keyword_id
WHERE keyword LIKE 'słowo kluczowe 1'
)
The result looks something like this:
location id | firm id | distance
loc1 | firm1 | 623452345
loc2 | firm1 | 546345145143
loc3 | firm2 | 3245211435
loc4 | firm2 | 333333
loc5 | firm2 | 233456825
In this example I would need loc1 for firm1 and loc4 for firm2. Adding Group By at the and of the query sort of helps, but gives whichever location comes first in a database. The following query gives the right minimal distances but not location id's:
SELECT id,auth_id, MIN(POW( l.x - 637340, 2 ) + POW( l.y - 486775, 2 )) as m
FROM localization l
WHERE l.auth_id IN (
SELECT fkx.auth_id
FROM keywords k
JOIN firms_keywords_xref fkx ON k.id = fkx.keyword_id
WHERE keyword LIKE 'słowo kluczowe 1'
)
GROUP BY l.auth_id
If I think somehow of joining those two queries ( (auth_id,distsq) IN (subquery with minimal distsq)) mysql says that distsq is unknown column, probably because it is calculated.
If you could point me in the right directions of how to do this properly? A temporary table maybe, so that distsq will not be calculated but retrieved from memory? But what is the impact of this on performance - this will be a query run with every page.
