Latitude and longitude sleep criteria

Question

With a MySQL table with over 20 million rows, is there any way with Hibernate to build criteria to get the nearest rows taking into account latitude and longitude?

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

Finally, can I get rows ordered by distance? Or too many lines?

Answer 1

Plan A With a large number of lines, INDEX(lat) is not a starter, in terms of performance, even with a band limit: AND lat BETWEEN 65 AND 69 . INDEX(lat, lng) not better, because the optimizer do not use both columns, even with AND lng BETWEEN...

Plan B. Your next choice will include lat and lng, plus a subquery. And version 5.6 would be helpful. This is 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 slightly better than Plan A.

Plan C. With millions of lines, you'll need my pizzeria algorithm . This includes a stored procedure for repeatedly probing a table, looking for enough lines. It also includes PARTITIONing to get a rough 2D index. A link has a link code that includes filtering on things like category.

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