Mysql query time massively affected by limit
The bigger the limit, the more of the 500K rows it will have to scan.  Hence the longer the query will take.
Yes, I see that the growth is not &quot;linear&quot;; there are many possible reasons for that, but let's ignore that and go for what you really want -- the query to be faster.
See the following for several ways to speed up the query.  In particular, pick the SPATIAL option.  http://mysql.rjweb.org/doc.php/find_nearest_in_mysql
Your indexes on lat and lng are not useful for this query, and tend to be not useful if you added a &quot;bounding box&quot; constraint in the WHERE clause.  However, INDEX(lat, lng), INDEX(lng, lat) is useful for a bounding box.
But the real problem seems to be that the Spatial test is in HAVING.  The Spatial index can be used if you move that test into the WHERE clause.  Yes, you will have to repeat the expression; too bad.
PS:  distance is not null is redundant; the test &lt; 30 fails for NULL.
Since there is no test on lat or lng, INDEX(parser_code, lat, lng) won't help.  You could add a bounding box test; that might let that composite index be even faster than SPATIAL; that would depend on the cardinality of parser_code.