Reusing MySQL Subquery

Question

Hi guys. I have a rather cumbersome SQL query:

SELECT username, users.photo_url, fp, dp,users.vid, 
       GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')), 
                                            AsBinary(location)))) AS distance
  FROM users 
    INNER JOIN venues ON users.vid = venues.vid
    LEFT JOIN deflects ON users.username = deflects.defender
  WHERE username NOT LIKE '{$mysql['username']}'
    AND username NOT LIKE '{$users['king']['username']}'
    AND venues.location IS NOT NULL
  HAVING 
    (distance <= (
      SELECT MAX(distance) AS max_distance 
        FROM (
            SELECT
                GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')), 
                                                   AsBinary(location))))
                AS distance
              FROM users 
                INNER JOIN venues ON users.vid = venues.vid
                LEFT JOIN deflects ON users.username = deflects.defender
              WHERE users.fp = 0
                AND username NOT LIKE '{$mysql['username']}'
                AND username NOT LIKE '{$users['king']['username']}'
                AND venues.location IS NOT NULL
                AND deflects.dp IS NULL
              ORDER BY distance LIMIT 5
          ) AS unfrozen)
     OR vid = '{$vid}')
  ORDER BY distance

I am currently reusing a lot of the same query twice - in particular, I would like to avoid this distance calculation more than once, but I cannot figure out how to do this. I use MySQL, so I don’t think regular table expressions are an option. Also, I ran into some problems with temporary tables. Is there a way to formulate this query so that I can reuse distance calculation?

In addition, I know that the method of calculating the distance does not give the true distance from a given geolate, geolong, but it is close enough for my purposes.

EDIT: And ... here's what I got, based almost entirely on Richard's answer below:

SELECT username, distance, photo_url, vid, fp, dp
  FROM (
    SELECT username, photo_url, vid, fp, dp,
        @d := distance AS distance,
        @c :=   if(fp = 0
                   AND dp IS NULL
                   AND @d>=@max, @c+1, @c),
        @max := if(fp = 0
                   AND dp IS NULL
                   AND @d>=@max
                   AND @c <= 5, @d, @max)
    FROM (SELECT @max:=0, @d:=null, @c:=0) AS MaxTally
      INNER JOIN (
          SELECT username, photo_url, users.vid, users.fp, deflects.dp,
                 GLength(LineStringFromWKB(LineString(AsBinary(PointFromText('POINT({$geolat} {$geolong})')),
                                                      AsBinary(location))))
                 AS distance
            FROM users
              INNER JOIN venues ON users.vid = venues.vid
              LEFT JOIN deflects ON users.username = deflects.defender
            WHERE username NOT LIKE '{$mysql['username']}'
              AND username NOT LIKE '{$users['king']['username']}'
              AND venues.location IS NOT NULL
            ORDER BY distance
        ) AllUsers
  ) AllUsersWithMaxTally
  WHERE vid = '{$vid}' OR distance <= @max
  ORDER BY distance

Thanks Richard!

Answer 1

Pseudocode - , , , . MySQL CRAZY!

SELECT
    username,
    distance
FROM
(
SELECT
    username,
    @d:=distance AS distance,
    @c   := if(fp = 0
               AND dp IS NULL
               AND @d>=@max, @c+1, @c),
    @max := if(fp = 0
               AND dp IS NULL
               AND @d>=@max
               AND @c <= 5, @d, @max) MaxOf5Dist
FROM (select @max:=-1000, @d:=null, @c:=0) M
INNER JOIN (
SELECT
    username, # others taken out for brevity
    users.fp, deflects.dp,
    GLength(LineStringFromWKB(LineString(AsBinary(
            PointFromText('POINT({$geolat} {$geolong})')), AsBinary(location))))
            AS distance
FROM users
CROSS JOIN venues ON users.vid = venues.vid
LEFT JOIN deflects ON users.username = deflects.defender
WHERE username NOT LIKE '{$mysql['username']}'
  AND username NOT LIKE '{$users['king']['username']}'
  AND venues.location IS NOT NULL
ORDER BY distance
) X
) Y
WHERE vid = '{$vid}' OR distance <= MaxOf5Dist
ORDER BY distance

Answer 2

. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html