SELECT and ORDER BY distances directly into a MySQL query .2

MySQLdistanceAs I was working on ShiningQuest, it occurred to me that you can directly input some calculations into MySQL queries. Well, I knew it was possible but it just struck me how powerful it was.

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
Share
  • It’s likely the where clause isnt optimizing it, so you could use “HAVING dist <= 5" after your ORDER BY

  • RomainVirmaux

    Thx for your advice i’ve made some improvements to my query 🙂