LP:938977 - Query performance with join/index super slow on MariaDB 5.3.4RC

A query that takes on average 0.05 seconds on mysql 5.1.47 with InnoDB plugin, is taking 8-9 seconds on MariaDB 5.3.4RC.

Went through each variable one by one, on the Maria Instance, and the MySQL instance ensuring they were the same with the same results.

Here is an explain on the MySQL DB
****************************************************************************************************************
MYSQL DB
****************************************************************************************************************
explain SELECT `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( 45.99290006, -64.76185116 ) AND getGeoMorton( 46.44290006, -64.31185116 ) AND `geo`.`latitude` BETWEEN 45.99290006 AND 46.44290006 AND `geo`.`longitude` BETWEEN -64.76185116 AND -64.31185116 AND `geo`.`vhost` = 123 AND `geo`.`moderationstatus` = 1 WHERE (1=1) AND (data_mediagallery.channelleft BETWEEN 158 AND 199) AND (data_mediagallery.context = 1) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.vhost = 123) ORDER BY `data_mediagallery`.`upload` DESC LIMIT 0, 20 ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> SELECT SQL_NO_CACHE `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( 45.99290006, -64.76185116 ) AND getGeoMorton( 46.44290006, -64.31185116 ) AND `geo`.`latitude` BETWEEN 45.99290006 AND 46.44290006 AND `geo`.`longitude` BETWEEN -64.76185116 AND -64.31185116 AND `geo`.`vhost` = 123 AND `geo`.`moderationstatus` = 1 WHERE (1=1) AND (data_mediagallery.channelleft BETWEEN 158 AND 199) AND (data_mediagallery.context = 1) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.vhost = 123) ORDER BY `data_mediagallery`.`upload` DESC LIMIT 0, 20 ;

20 rows in set (0.12 sec)

****************************************************************************************************************
Maria DB
****************************************************************************************************************
MariaDB [sabretooth2]> explain SELECT `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( 45.99290006, -64.76185116 ) AND getGeoMorton( 46.44290006, -64.31185116 ) AND `geo`.`latitude` BETWEEN 45.99290006 AND 46.44290006 AND `geo`.`longitude` BETWEEN -64.76185116 AND -64.31185116 AND `geo`.`vhost` = 123 AND `geo`.`moderationstatus` = 1 WHERE (1=1) AND (data_mediagallery.channelleft BETWEEN 158 AND 199) AND (data_mediagallery.context = 1) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.vhost = 123) ORDER BY `data_mediagallery`.`upload` DESC LIMIT 0, 20 ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [sabretooth2]> SELECT `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( 45.99290006, -64.76185116 ) AND getGeoMorton( 46.44290006, -64.31185116 ) AND `geo`.`latitude` BETWEEN 45.99290006 AND 46.44290006 AND `geo`.`longitude` BETWEEN -64.76185116 AND -64.31185116 AND `geo`.`vhost` = 123 AND `geo`.`moderationstatus` = 1 WHERE (1=1) AND (data_mediagallery.channelleft BETWEEN 158 AND 199) AND (data_mediagallery.context = 1) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.vhost = 123) ORDER BY `data_mediagallery`.`upload` DESC LIMIT 0, 20 ;

20 rows in set (8.24 sec)

getGeoMorton

CREATE DEFINER=`sabretooth`@`%` FUNCTION `getGeoMorton`( `lat` DOUBLE, `lng` DOUBLE ) RETURNS bigint(20) unsigned
DETERMINISTIC
BEGIN
DECLARE bit, morton, pos BIGINT UNSIGNED DEFAULT 0;

SET @lat = CAST((lat + 90) * 11930464 AS UNSIGNED);
SET @lng = CAST((lng + 180) * 11930464 AS UNSIGNED);

SET @lat = IF( @lat < 0, 0, @lat );
SET @lng = IF( @lng < 0, 0, @lng );

SET bit = 1;

WHILE bit <= @lat || bit <= @lng DO
IF(bit & @lat) THEN SET morton = morton | ( 1 << (2 * pos + 1)); END IF;
IF(bit & @lng) THEN SET morton = morton | ( 1 << (2 * pos)); END IF;

SET pos = pos + 1;

SET bit = 1 << pos;
END WHILE;

RETURN morton;
END;

GEO Table

Data_mediagallery table