[MDEV-9654] query takes a long time Created: 2016-02-28  Updated: 2016-05-18  Resolved: 2016-05-18

Status: Closed
Project: MariaDB Server
Component/s: GIS
Affects Version/s: 10.0.23
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Pavel Dvorak Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

10.0.23-MariaDB-1~wheezy-log - mariadb.org binary distribution

Attachments: File address_country.sql     Zip Archive address_country_shape.zip     File data.sql    

 Description   

this query takes about 10 minutes

SELECT ac.id, ac.iso_n3, ac.iso_a2, ac.iso_a3, ac.dial_code, ac.zip_format, ac.name_long, ac.name, ac.name_un_long, ac.name_un, ac.default_lang_id, ac.default_currency_id, ac.states, ac.region_type_primary, ac.region_type_secondary, ac.continent, ac.neighbours, ac.languages, ac.tld, ac.currency_code, ac.dummy FROM `global`.`address_country` ac WHERE ac.dummy IS NULL AND ac.id IN(SELECT DISTINCT id FROM `global`.`address_country_shape` acs WHERE ST_INTERSECTS(shape,@var56d318005292f)) ORDER BY ac.name

the problem is with st intersects part
address_country_shape has about 300 rows
in @var there some polyline, should not be more that 1000 points
it usually works but with some polyline it took a long time
it says Copying to tmp table and the cpu is on 100%

 Comments   
Comment by Elena Stepanova [ 2016-02-29 ]

dvorak, could you please paste the output of SHOW CREATE TABLE and SHOW INDEX for both tables?
Better still if you can attach the data dump.

Comment by Pavel Dvorak [ 2016-02-29 ]

it says File "address_country.sql" was not uploaded
Could not save attachment to storage: Unable to create target directory /data/jira/data/attachments/MDEV/10000/MDEV-9654

Comment by Elena Stepanova [ 2016-02-29 ]

Sorry about that, we had JIRA migration last weekend, apparently there are still some flaws. I'll let you know as soon as it gets fixed.

Comment by Elena Stepanova [ 2016-02-29 ]

dvorak, it should work now, could you please try again?

Comment by Elena Stepanova [ 2016-02-29 ]

dvorak, thanks. Can you also provide an example of @var which causes the problem? Please also attach your cnf file(s) just in case there is something that affects the behavior.

Comment by Pavel Dvorak [ 2016-02-29 ]

i don't have it now so i have to make it when it happens again

Comment by Pavel Dvorak [ 2016-03-07 ]

i think i know where's the problem
@var uses group cocat to make the data
so if it exceeds group concat max length, it thinks it makes coord like 0,0 so that's why it takes such long time

Comment by Elena Stepanova [ 2016-03-07 ]

dvorak,
group_concat_max_length is supposed to truncate the string, not to change its contents; so, probably I misunderstand your point. Could you please provide an actual example of such unfortunate value of @var?

Thanks.

Comment by Pavel Dvorak [ 2016-03-30 ]

i found it
there's a corrupted polyline and it takes 60 seconds

SET @var56fba8e00b542= (SELECT GEOMFROMTEXT(CONCAT('LINESTRING (', GROUP_CONCAT(CONCAT(lon,' ',lat)  ),')'))
 
FROM `gps_CCIGC`.`data` dt
 
WHERE dt.unit_id='7500' AND dt.time BETWEEN '2016-03-29 22:00:00' AND '2016-03-30 22:20:00' AND  ( (dt.ignition='1' ) OR ( dt.movement='1' ) OR ( dt.speed>'10') )
 
ORDER BY dt.time ASC, dt.end ASC, dt.id ASC)
 
 
 
SELECT ac.id, ac.iso_n3, ac.iso_a2, ac.iso_a3, ac.dial_code, ac.zip_format, ac.name_long, ac.name, ac.name_un_long, ac.name_un, ac.default_lang_id, ac.default_currency_id, ac.states, ac.region_type_primary, ac.region_type_secondary, ac.continent
 
FROM `global`.`address_country` ac
 
WHERE ac.dummy IS NULL AND ac.id IN(SELECT DISTINCT id
 
FROM `global`.`address_country_shape` acs
 
WHERE ST_INTERSECTS(shape,@var56fba8e00b542))
 
ORDER BY ac.name

Comment by Elena Stepanova [ 2016-03-30 ]

For me, the query takes less than a second:

MariaDB [test]> SET @var56fba8e00b542= (SELECT GEOMFROMTEXT(CONCAT('LINESTRING (', GROUP_CONCAT(CONCAT(lon,' ',lat)  ),')'))
 
    -> FROM `gps_CCIGC`.`data` dt
 
    -> WHERE dt.unit_id='7500' AND dt.time BETWEEN '2016-03-29 22:00:00' AND '2016-03-30 22:20:00' AND  ( (dt.ignition='1' ) OR ( dt.movement='1' ) OR ( dt.speed>'10') )
 
    -> ORDER BY dt.time ASC, dt.end ASC, dt.id ASC);
 
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+----------------------------------+
 
| Level   | Code | Message                          |
 
+---------+------+----------------------------------+
 
| Warning | 1260 | Row 55 was cut by GROUP_CONCAT() |
 
+---------+------+----------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT ac.id, ac.iso_n3, ac.iso_a2, ac.iso_a3, ac.dial_code, ac.zip_format, ac.name_long, ac.name, ac.name_un_long, ac.name_un, ac.default_lang_id, ac.default_currency_id, ac.states, ac.region_type_primary, ac.region_type_secondary, ac.continent
 
    -> FROM `global`.`address_country` ac
 
    -> WHERE ac.dummy IS NULL AND ac.id IN(SELECT DISTINCT id
 
    -> FROM `global`.`address_country_shape` acs
 
    -> WHERE ST_INTERSECTS(shape,@var56fba8e00b542))
 
    -> ORDER BY ac.name;
 
+----+--------+--------+--------+-----------+------------+------------------------+---------+---------------------+---------+-----------------+---------------------+--------+---------------------+-----------------------+-----------+
 
| id | iso_n3 | iso_a2 | iso_a3 | dial_code | zip_format | name_long              | name    | name_un_long        | name_un | default_lang_id | default_currency_id | states | region_type_primary | region_type_secondary | continent |
 
+----+--------+--------+--------+-----------+------------+------------------------+---------+---------------------+---------+-----------------+---------------------+--------+---------------------+-----------------------+-----------+
 
| 76 |    250 | FR     | FRA    | +33       | ^(\d{5})$  | Francouzská republika  | Francie | the French Republic | France  |            NULL |                NULL |      0 | department          | arrondissement        | EU        |
 
+----+--------+--------+--------+-----------+------------+------------------------+---------+---------------------+---------+-----------------+---------------------+--------+---------------------+-----------------------+-----------+
 
1 row in set (0.28 sec)
 
 
 
ariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.0.23-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Please attach your cnf file(s).

Comment by Pavel Dvorak [ 2016-03-31 ]

so i guess GROUP_CONCAT is the problem
but strange that you don't have the slowdown

Comment by Elena Stepanova [ 2016-04-18 ]

dvorak,
What do you mean by "GROUP_CONCAT is the problem"? It's supposed to truncate lines to group_concat_max_len, so it does. It's interesting indeed though why you get the slowdown and I don't. Would you be able to dump the variable to a file and attach it? Maybe our GROUP_CONCATs return differently truncated lines.

Comment by Elena Stepanova [ 2016-05-18 ]

Please comment to re-open if you have further information on the issue.

Generated at Thu Feb 08 07:36:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.