Tengo una columna espacial denominada SHAPE con SRID 4269 e índice espacial. Cuando hago la consulta
select geoid10 as zipcode from tl_2019_us_zcta510 where st_intersects(ST_GeomFromText('POINT(30.330280 -82.759009)',4269),SHAPE);tarda 2 minutos en funcionar. La tabla contiene 33k registros.
Revisé la consulta si está usando el índice
explain select geoid10 as zipcode from tl_2019_us_zcta510 where st_intersects(ST_GeomFromText('POINT(30.330280 -82.759009)',4269),SHAPE);y obtengo el resultado
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | tl_2019_us_zcta510 | NULL | ALL | NULL | NULL | NULL | NULL | 28206 | 100.00 | Using where | +----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+Esto muestra claramente que la consulta no utiliza un índice espacial.
Ejecuté la misma consulta en Mysql 5.7 y está usando un índice espacial.
Puede alguien ayudarme con esto. ¿Hay algún otro cambio de configuración que deba tener en cuenta?
(Esto no responde la pregunta, pero puede agregar alguna idea).
El registro de cambios 8.0.4 dice (agregué negrita):
Cambio incompatible: anteriormente , estas funciones espaciales ignoraban el sistema de referencia espacial (SRS) para los argumentos geométricos y los resultados calculados en un plano cartesiano. Ahora admiten cálculos para argumentos geométricos que especifican un SRS geográfico: ST_Distance_Sphere(), ST_IsSimple(), ST_IsValid(), ST_Length().
Anteriormente, estas funciones espaciales ignoraban el SRS para cualquier argumento geométrico y calculaban los resultados en un plano cartesiano. Ahora producen un error cuando se invocan con argumentos geométricos que especifican un SRS geográfico: ST_Area(), ST_Buffer(), ST_Centroid(), ST_ConvexHull(), ST_Difference(), ST_Envelope(), ST_Intersection() , ST_IsClosed(), ST_MakeEnvelope( ), ST_Simplify(), ST_SymDifference(), ST_Union(), ST_Validate().
Anteriormente, estas funciones espaciales permitían argumentos geométricos con un SRS indefinido. Ahora producen un error cuando se invocan con argumentos de geometría que tienen un SRS indefinido: ST_Dimension(), ST_Distance_Sphere(), ST_EndPoint(), ST_ExteriorRing(), ST_GeometryN(), ST_GeometryType(), ST_InteriorRingN(), ST_IsEmpty(), ST_IsSimple( ), ST_IsValid(), ST_Length(), ST_NumGeometries(), ST_NumInteriorRing(), ST_NumInteriorRings(), ST_NumPoints(), ST_PointN(), ST_StartPoint(), ST_SwapXY(), ST_X(), ST_Y().
Anteriormente, la función espacial ST_GeoHash() aceptaba puntos con cualquier SRID. ST_GeoHash() ahora acepta solo puntos con SRID 0 o 4326. Nota
Si los datos espaciales contienen valores geométricos que ahora son interpretados de manera diferente por las funciones que se acaban de enumerar, las consultas existentes que utilizan estas funciones arrojarán resultados diferentes, en comparación con las versiones anteriores de MySQL.
Mi comentario: ¿Qué es 4269? ¿Quizás solo se maneja 4326? ¿Funcionará más rápido con 4326?
Lo resolví después de leer este: https://dba.stackexchange.com/questions/260757/mysql-8-not-using-spatial-index
Básicamente, debe definir un SRID predeterminado para su columna en la creación de la tabla (o con ALTER).
En su caso, la columna geoid10 en la tabla tl_2019_us_zcta510 debe definirse con SRID 4269, luego debe usar correctamente el índice espacial. Funcionó para mí.
Esta es la respuesta correcta citada por Nikita en dba.stackexchange.com:
El atributo SRID crea una columna espacial restringida por SRID, lo que tiene estas implicaciones:
La columna solo puede contener valores con el SRID dado. Los intentos de insertar valores con un SRID diferente producen un error.
El optimizador puede usar índices ESPACIALES en la columna. Consulte la Sección 8.3.3, “Optimización del índice ESPACIAL”.
Las columnas espaciales sin atributo SRID no están restringidas por SRID y aceptan valores con cualquier SRID. Sin embargo, el optimizador no puede usar índices ESPACIALES en ellos hasta que la definición de la columna se modifique para incluir un atributo SRID, lo que puede requerir que primero se modifique el contenido de la columna para que todos los valores tengan el mismo SRID.