Hibernate criteria by latitude and longitude

Question

Having a MySQL table with more than 20 millions of rows, there is some way with Hibernate to build a criteria in order to get nearest rows given a latitude and longitude?

Using Criteria would be great because I need to use more filters (price, category, etc).

Finally, it's posible get the rows ordered by distance? Or there are too much rows?

Answer 1

Plan A With a large number of rows, INDEX(lat) is a non-starter, performance-wise, even with restricting to a stripe: AND lat BETWEEN 65 AND 69. INDEX(lat, lng) is no better because the optimizer would not use both columns, even with AND lng BETWEEN...

Plan B Your next choice will involve lat and lng, plus a subquery. And version 5.6 would be beneficial. It's something like this (after including INDEX(lat, lng, id)):

SELECT ... FROM (
    SELECT id FROM tbl
        WHERE lat BETWEEN... 
          AND lng BETWEEN... ) x
    JOIN tbl USING (id)
    WHERE ...;

For various reasons, Plan B is only slightly better than Plan A.

Plan C With millions of rows, you will need my pizza parlor algorithm. This involves a Stored Procedure to repeatedly probe the table, looking for enough rows. It also involves PARTITIONing to get a crude 2D index. The link has reference code that includes filtering on things like category.

Plans A and B are O(sqrt(N)); Plan C is O(1). That is, for Plans A and B, if you quadruple the number of rows, you double the time taken. Plan C does not get slower as you increase N.