1

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) |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+
4
  • in postgis there exists st_subdivide (and sf in R), i don't know about mysql but maybe there's an equivalent; how many vertices do your polygons have (better said: how much memory do they use? blog.cleverelephant.ca/2019/11/… – Elio Diaz Aug 17 '20 at 18:26
  • @ElioDiaz I'm not sure how to get the total number of points in the polygons column. They aren't very large, so I can't imagine there is to many per row. – John C Aug 17 '20 at 20:06
  • I mean something like ST_MemSize(the_geom) in postgis, polygons may not be large but very detailed and this increases their memory size – Elio Diaz Aug 17 '20 at 21:15
  • They are the school zone boundaries for Texas – John C Aug 17 '20 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.