Myujth

Why mysql don't create spatial index (does it mean if spatial index length=0 index was not created)?
OR maybe

Why query don't use spatial index (index length isn't an indicator)?

Mysql version 8.0.13 (on 5.7.24 work well)

CREATE TABLE `boundaries`.`nodes_coastline` (
 `id` bigint(64) NOT NULL,
 `coords` point NOT NULL,
 `coordsSRID` point NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;
ALTER TABLE `boundaries`.`nodes_coastline` 
 ADD PRIMARY KEY (`id`), 
 ADD SPATIAL KEY `coords` (`coords`), 
 ADD SPATIAL KEY `coordsSRID` (`coordsSRID`);
ALTER TABLE `boundaries`.`nodes_coastline` MODIFY `id` bigint(64) NOT NULL AUTO_INCREMENT;

SELECT N.id AS node_id,
 NC.id AS coastline_node_id,
 ST_DISTANCE_SPHERE(POINT(N.longitude, N.latitude), NC.coords) / 1000 AS distanceKm
FROM nodes N,
 way_nodes WN,
 nodes_coastline NC
WHERE ST_CONTAINS(
 ST_GEOMFROMTEXT(
 ST_ASTEXT(
 ST_MAKEENVELOPE(
 POINT((N.longitude + 1 / 111.1), (N.latitude + 1 / 111.1)),
 POINT((N.longitude - 1 / 111.1), (N.latitude - 1 / 111.1))
 )
 ),
 4326
 ),
 NC.coordsSRID
 ) 
 AND WN.node_id = N.id 
 AND WN.id = 586687989
ORDER BY distanceKm
LIMIT 1;

EXPLAIN

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | WN | NULL | ref | PRIMARY,ix_node_id | PRIMARY | 8 | const | 122 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | N | NULL | eq_ref | PRIMARY | PRIMARY | 8 | WN.node_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | NC | NULL | ALL | NULL | NULL | NULL | NULL | 55400618 | 100.00 | Using where; Using join buffer (Block Nested Loop)|

EDIT

I found the answer:

1. Field was not created with SRID 4326, but data were.


2. ST_Contains function has not static geometry. In my query POINT(N.longitude, N.latitude).

But, I need dynamic POINTs. So, how to rewrite this query to support it?