how to sort rows by a distance from a given point, mysql?

Question

I'm trying to get 100 points from my table with a lowest distance to a given point.

I'm using

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` FROM `ip_group_city` ORDER BY distance LIMIT 100

(Yeah, that's painful. I've just googled it. I have no idea how to measure distance in MySQL correctly)

It takes very long time for execute. EXPLAIN says that there are no possible_keys.

I created a SPATIAL index on the pt column:

CREATE SPATIAL INDEX sp_index ON  ip_group_city (pt);

Though I don't really know how to use it correctly. Can you please help me?

Answer 1

Because you don't have WHERE clause therefore no affected index. I think you should improve this query by add using MBR_ (MySQL 5.0 or later) or ST_ functions (MySQL 5.6 or later). Something like:

SELECT *, GLENGTH(
            LINESTRINGFROMWKB(
              LINESTRING(
                ASBINARY(
                  POINTFROMTEXT("POINT(40.4495 -79.988)")
                ),
                ASBINARY(pt)
              )
            )
          )
 AS `distance` 
FROM `ip_group_city` 
WHERE
MBRWithin(
        pt, -- your point
        GeomFromText('Polygon( -- your line (in polygon format) from pt to target point 
                        (
                            #{bound.ne.lat} #{bound.ne.lng}, --North East Lat - North East Long
                            #{bound.ne.lat} #{bound.sw.lng}, --North East Lat - South West Long
                            #{bound.sw.lat} #{bound.sw.lng}, --
                            #{bound.sw.lat} #{bound.ne.lng},
                            #{bound.ne.lat} #{bound.ne.lng}
                        )
                    )')
      )
ORDER BY distance LIMIT 100