We are having what seems to be a fairly large mysql performance issue on trying to run a fairly simple update statement. We have a table(1.8mil) with houses that contains a Lat+Long geometry point column(geo), and then a table(6k) that has a list of schools with a boundary geometry polygon column(boundary). We have spatial indexes on both, we are trying to set the school's id, that contains the point, to the house table with the update. The update is taking 1 hour and 47 minutes to update 1.6mil records. In other systems I have used in my paste experience, something like that would take just a few minutes. Any recommendations?
I have posted this same question in the DBA SE site as well, as it is very much a GIS & DBA question.
CREATE TABLE houses (
ID int PRIMARY KEY NOT NULL,
Latitude float DEFAULT NULL,
Longitude float DEFAULT NULL,
geo point GENERATED ALWAYS AS (st_srid(point(ifnull(`Longitude`,0),ifnull(`Latitude`, 0)),4326)) STORED NOT NULL,
SPATIAL INDEX spidx_houses(geo)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE schoolBound (
ID int PRIMARY KEY NOT NULL,
BOUNDARY GEOMETRY NOT NULL,
reference VARCHAR(200) DEFAULT NULL,
type bigint DEFAULT NULL,
INDEX idx_reference(reference),
INDEX idx_type(type),
SPATIAL INDEX spidx_schoolBound(BOUNDARY)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;
-- type 4 means it's a elementary
Update houses hs
INNER JOIN schoolBound AS sb ON ST_Contains(sb.boundary, hs.geo) AND sb.type = 4
SET hs.elementary_nces_code = sb.reference
The explain seems to show that it is not going to use the spatial index for schoolBound.
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
| 1 | SIMPLE | sb | NULL | ALL | NULL | NULL | NULL | NULL | 6078 | 10.00 | Using where |
| 1 | UPDATE | hs | NULL | ALL | spidx_houses | NULL | NULL | NULL | 1856567 | 100.00 | Range checked for each record (index map: 0x4) |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
