In this case, I play a character on ShiningQuest with coordinates x,y to cast a spell on itself. This spell affects a 5-unit radius zone around the character – and I want to select every character into the zone.
Here is the MySQL query (assuming my character_id = 1 and the spell effect radius = 5) :
SELECT SQRT(POW(c1.x-c2.x, 2) + POW(c1.y-c2.y, 2)) AS dist, c2.* FROM characters AS c1, characters AS c2 WHERE c1.id = 1 AND SQRT(POW(c1.x-c2.x, 2) + POW(c1.y-c2.y, 2)) <= 5 ORDER BY dist
I’m SELECTing the distance between my character and every character in the zone (as ‘dist’) and using it to ORDER my results (it’s useless in this particular case but fun to show here). I can’t reuse “dist” in the WHERE clause and I have to redo the calculation.
[EDIT 2/8/2013] Thx for your advices on FB and comments, here’s the list of improvements for a more efficient query on a larger database :
- I’ve removed the SQRT function ;
- I’ve added HAVING, in order to be able to reuse “dist” instead of redoing calculations in the WHERE clause ;
- and I pre-filter results in the WHERE clause.
SELECT POW(c1.x-c2.x, 2) + POW(c1.y-c2.y, 2) AS dist, c2.* FROM characters AS c1, characters AS c2 WHERE c1.id = 1 AND c2.x BETWEEN c1.x - 5 AND c1.x + 5 AND c2.y BETWEEN c1.y - 5 AND c1.y + 5 HAVING dist <= 25 ORDER BY dist