¿Cuál es la forma más rápida de buscar tablas grandes para puntos dentro del radio MySQL(latitud, longitud)

query-optimization geospatial

Question

Actualmente tengo algunas tablas con más de 100k filas. Estoy tratando de buscar los datos de la siguiente manera.

SELECT
*, SQRT(POW(69.1 * (latitude - ''49.1044302''), 2) + POW(69.1 * (''-122.801094'' - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM stops
HAVING distance < 5
ORDER BY distance limit 100

Pero actualmente este método se ralentiza con alta carga. Algunas consultas tardan más de 20 segundos en completarse.

Si alguien conoce alguna forma mejor de optimizar esto, sería genial.

Answer 1

Bueno, en primer lugar, si tiene muchos datos geoespaciales, debería usar las extensiones geoespaciales de mysql en lugar de cálculos como este. Luego puede crear índices espaciales que acelerarían muchas consultas y no tiene que escribir consultas prolongadas como la anterior.

Usar una comparación con ST_Distance o crear una geometría con el radio de interés junto con ST_within podría brindarle buenos resultados y podría ser mucho más rápido que el actual. Sin embargo, la mejor y más rápida forma de lograr esto, ST_Dwithin aún no está implementado en mysql.

Answer 2

Los índices espaciales definitivamente dependen de la versión de MySQL. Nuestros sitios también buscan lat / lons, pero estamos utilizando una versión anterior de MySQL (5.1-algo) (sin índices espaciales). Su consulta es similar a la nuestra, pero la nuestra se basa en radianes. Dependiendo de sus necesidades exactas, puede optimizarlo (de lo que tiene) bastante.

1. Definitivamente elimine el sqrt () de la consulta de la base de datos, tiene que calcular para cada fila, solo calcularlo al final cuando se muestra la distancia real a un usuario, también cuadrar el "tener distancia <5" a "<25" . Sqrt es costoso y se mueve fácilmente a donde no necesita ser calculado.
2. entrecomilla el lat / lon ''49 .1044302 ''para que sea estrictamente un int y realice la verificación de tipo lat / lon fuera de la consulta. Esto no lo acelerará, pero evitará el lanzamiento incorrecto debido a espacios en blanco falsos en su variable lat / lon.
3. Convierta el 5 en la diferencia real de grados de lat / lote en cada dirección + / 5 para producir un rango límite (una caja, por así decirlo). Agréguelo a la parte "dónde" de la consulta: esta restricción le dará un conjunto de filas de resultados prácticamente exactos y reducidos, básicamente el rango x e y +/- en el lat y lon es el límite superior de los resultados - las diagonales que se calculan solo matizan ligeramente los resultados y sus distancias.
4. Mueva la mayor parte de las matemáticas fuera de la selección y dónde: tendrá que escanear toda la tabla y crear una temporal, calculada en cada fila, para obtener esos resultados. Muchas de las matemáticas en la consulta se pueden convertir en una constante.
5. Acelere la reducción de la fila (el cuadro de selección) aún más bajando la resolución en el lat / lon (copiando) en otro campo (y quizás multiplique por 10 o 100 y convierta a un INT) y agregue un índice en ese campo, y usando ese campo con +/- límites en donde, al menos entonces podrá usar una clave: mysql puede reducir y esos resultados mucho más rápido.

Al menos así es como lo hacemos.