Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Incomplete
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
You can use the data files from https://bugs.launchpad.net/maria/+bug/938977 to reproduce this.
Mysql 5.1.49 and MySQL 5.1.61
Handle this query in 5 seconds on my EC2 m2.4xlarge instance.
I was getting ready to move to MariaDB on production.
Installed mariadb 5.3.5, ran this query, waited 3+ minutes, and it still didn't complete, so had to revert back to mysql 5.1
SELECT `data_mediagallery`.`filetype` AS `filetype`, `data_mediagallery`.`id` AS `id`, `data_mediagallery`.`metadata` AS `metadata`, `data_mediagallery`.`title` AS `title`, `data_mediagallery`.`vhost` AS `vhost` FROM data_mediagallery
INNER JOIN
`geo` USE INDEX ( `morton` )
ON
`data_mediagallery`.`id` = `geo`.`foreignid`
AND
`geo`.`datatype` = 1
AND
`geo`.`morton` BETWEEN getGeoMorton( -89.055209, -173.81254 ) AND getGeoMorton( 89.505209, 173.36254 )
AND
`geo`.`latitude` BETWEEN -89.055209 AND 89.505209
AND
`geo`.`longitude` BETWEEN -173.81254 AND 173.36254
AND
`geo`.`vhost` = 123
WHERE (data_mediagallery.context = 1) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.vhost = 123) ORDER BY `data_mediagallery`.`upload` DESC LIMIT 1000, 500;
It's very similar to the query in the previous bug.
The geoMorton function is available there also.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 954439
Re: Painfully Slow Query on MariaDB 5.3.5 - Inner Join
Hi,
I am not getting quite the same difference.
(for the baseline, the query from the previous, already fixed, bug on the same machine with the same data takes ~15 sec as the 1st execution, and about 0.15 sec on the next execution, both on MariaDB and MySQL)
Here is what I have for the new query (all are "warm" results):
InnoDB tables:
MariaDB 5.3.5: ~ 1 min 8 sec
MySQL 5.1.61, built-in InnoDB: ~ 1 min 11 sec
MySQL 5.1.61, InnoDB plugin: ~ 47 sec
MariaDB 5.2.10: ~ 1 min 2 sec
MyISAM tables:
MariaDB 5.3.5: ~ 43 sec
MySQL 5.1.60: ~ 31 sec
query_cache_size=0.
Results were volatile, and cold ones were all over the place (from 1 min to 6+ min). But the tendency is that MariaDB 5.3.5 indeed executes the query slower than MySQL+InnoDB-plugin, which, I presume, you are using; but it is not as much slower as you have seen (not 5 sec vs 3+ min). Could you please confirm that your results relate to the same query and same basic conditions (same data, configuration, first execution vs not first, etc.); and if it is so, provide your cnf file, so we make sure we are targeting the right problem?
Thanks.