1

I have a table area which contains ~300,000 rows. It is defined as follows:

CREATE TABLE `area` (
  `area_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `boundary` geometry NOT NULL,
  `name` varchar(255) NOT NULL,
  `area_type_id` bigint(20) NOT NULL,
  PRIMARY KEY (`area_id`),
  KEY `FK_7r1ppwayxh76te8c6jq3nrd71` (`area_type_id`),
  SPATIAL KEY `boundary` (`boundary`)
) ENGINE=InnoDB AUTO_INCREMENT=311998 DEFAULT CHARSET=utf8;

The execution times on MySQL 5.6.24 and the latest GA 5.7.9 vary greatly (two orders of magnitude) for the following query:

set @p = st_GeomFromText('POINT(14.822664 56.858916)');
select area_id from area where st_within(@p, boundary);

Although the result of the query ran on either system is the same, the execution times are not:

| MySQL 5.6.24-log (x86/Win32) | MySQL 5.7.9-log (x86_64/Win64) |
| ---------------------------- | ------------------------------ |
|                   0.0553 sec |                     8.5342 sec |

So, the newer MySQL version is 154 times slower. Am I doing something wrong?

The explain looks as follows:

| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra
| -- | ----------- | ----- | ---------- | ----- | ------------- | -------- | ------- | ---- | ---- | -------- | -----
| 1  | SIMPLE      | area  | NULL       | range | boundary      | boundary | 34      | NULL | 1    | 100.00   | Using where

Edit: Here is a dump of the table which I used (~821mb).

6
  • dump - protected – a_vlad Nov 23 '15 at 14:17
  • Dump should work now I guess. – user654123 Nov 23 '15 at 15:23
  • it interesting - plan the same, query statistic show - 12 rows examined, 4 returned to client, but time very long on 5.7 – a_vlad Nov 23 '15 at 19:35
  • @a_vlad: So you can confirm the slow speed? – user654123 Nov 23 '15 at 19:55
  • 1
    Btw. I am having another issue with this which I posted on StackOverflow earlier. I think I should file a bug report. – user654123 Nov 23 '15 at 20:18

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.