0

I'm having a very peculiar instance wherein we recently had to upgrade our database from MySQL 5.7 to 8.0, and I had to rewrite some queries.

If my query is complicated enough, I usually prepare writing it in DBeaver, and then eventually put it into our Laravel-based (PHP) application, which of course uses PDO to connect to MySQL.

I noticed that this query was frequently taking 4-5 seconds to run, while it takes ~30ms in DBeaver. (I've simplified it from the original, and kept only the part that makes it slow.)

The slow query is this:

select
    COUNT(location) as count,
    ST_Latitude(location) as lat,
    ST_Longitude(location) as lng
from
    `LocationCache`
where
  MBRContains(ST_GeomFromText('Polygon((
                    -116.41480990949 40.923245158437,
                    -116.41480990949 38.44220035439,
                    -120.68677260991 38.44220035439,
                    -120.68677260991 40.923245158437,
                    -116.41480990949 40.923245158437
                    ))', 4326, 'axis-order=long-lat'),
              location)
group by
    `location`;

I found also that if I connect directly to my db host via SSH and run the mysql command line client, the query is equally as slow.

If I remove the COUNT() and the group by, and even the ST_Latitude/ST_Longitude function calls, it is still slow -- so it seems to be with the geospatial where condition itself.

The CREATE table syntax (clipped of irrelevant columns/indexes) looks like this:

CREATE TABLE `LocationCache` (
  `media_id` bigint unsigned NOT NULL,
  `location` point NOT NULL /*!80003 SRID 4326 */ COMMENT 'Location point',
  UNIQUE KEY `media_id_unique` (`media_id`),
  SPATIAL KEY `location_spatialindex` (`location`),
  CONSTRAINT `1` FOREIGN KEY (`media_id`) REFERENCES `Media` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

To be clear I am accessing the same MySQL server from PHP, command line, and DBeaver, which is running in a local Docker container (image mysql/mysql-server:8.0.32). I have tried using my host computer's mysql command line program, and SSHing into the docker container and running its local mysql command, but both are slow.

I showed this to a few people who had never heard of a query being slow only using PHP and fast in my GUI, and they recommended installing MySQL Workbench and trying it there. Indeed: in MySQL Workbench it was fast as well!

Using EXPLAIN on the query in DBeaver vs CLI gives the same explanation. (Both of them specify the location_spatialindex key as a possible_key but neither uses it in the key column for some reason?)

My only guess is that there is something different about the MySQL driver that Workbench/DBeaver uses compared to PDO/CLI, but I'm really at a loss here.

Any help appreciated! Thanks in advance!

EDIT: Table has ~700,000 rows. And I'd never heard of EXPLAIN ANALYZE SELECT (Thanks @RickJames), but the output is this:

-> Table scan on <temporary>  (actual time=5032.824..5032.824 rows=6 loops=1)
    -> Aggregate using temporary table  (actual time=5032.823..5032.823 rows=6 loops=1)
        -> Filter: mbrcontains(<cache>(st_geomfromtext('Polygon((\n                    -116.41480990949 40.923245158437,\n                    -116.41480990949 38.44220035439,\n                    -120.68677260991 38.44220035439,\n                    -120.68677260991 40.923245158437,\n                    -116.41480990949 40.923245158437\n                    ))',4326,'axis-order=long-lat')),LocationCache.location)  (cost=73576.76 rows=135833) (actual time=301.622..5024.938 rows=1343 loops=1)
            -> Table scan on LocationCache  (cost=73576.76 rows=663100) (actual time=0.022..511.299 rows=703852 loops=1)
5
  • 1
    How many rows in the table?
    – Rick James
    Oct 19, 2023 at 21:12
  • 1
    What does EXPLAIN ANALYZE SELECT ... say?
    – Rick James
    Oct 19, 2023 at 21:13
  • @RickJames ~700,000 rows! And I updated with answer to the EXPLAIN ANALYZE SELECT. Which sounds like it's saying DBeaver has secretly created a temporary table at some point in the past and using that??
    – Offlein
    Oct 19, 2023 at 21:28
  • I am puzzled by group by location. This implies that there are multiple identical location values in the table. That is multiple items at the same POINT. MySQL made the temp table because GROUP BY needed; try removing the group by location.
    – Rick James
    Oct 19, 2023 at 22:57
  • There are indeed multiple identical location values in the table. Other columns would be different, and I am trying to cluster all the records that share the same location values within a bounding rectangle. That said, actually in the last hour, I have solved this problem! I will answer my own question in a bit, but it seems tuning some server params (probably most notably tmp_table_size) allowed for the table to be created in all cases, and it, afterward a single slow query, reliably now uses the key for all queries somehow.
    – Offlein
    Oct 19, 2023 at 23:34

1 Answer 1

0

I have solved this problem with some advice from another DBA I talked to. (And thanks to user @Rick James for suggesting to run EXPLAIN ANALYZE SELECT.) It is not 100% clear what causes this, but at least I can reliably fix it.

It would appear that an index (...at least, this spatial index.) isn't inherently populated (or otherwise cannot be used for some reason), if there are not sufficient resources in the server to do so.

Further, it is not clear why using DBeaver/MySQL Workbench would somehow compensate for this lack of resources when the mysql command line application could not.

Regardless, I modified the following three global variables:

  • max_heap_table_size was set to 64M
  • tmp_table_size was set to 64M
  • innodb_buffer_pool_size was set to 4G (at least for now in my local Docker MySQL server).

It's unclear which was the silver bullet, but the effect was nearly instantaneous. If I run the same query in the mysql CLI application, it actually required the 5 seconds one final time (I guess to build the temporary table and/or populate the index somehow?), but after that, all similar queries are near-instantaneous and, if you use EXPLAIN show that they are using that spatial index as I initially had hoped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.