|
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 ;
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
---
| 1 |
SIMPLE |
geo |
range |
morton |
morton |
18 |
NULL |
32648 |
Using where; Using temporary; Using filesort |
| 1 |
SIMPLE |
data_mediagallery |
eq_ref |
PRIMARY,vhostupload,vhostmodstatus,complexfiletype,vhostcontext,hockeynews_custom,externalcomments,vhostchannel |
PRIMARY |
4 |
sabretooth2.geo.foreignid |
1 |
Using where |
---
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 ;
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
---
| 1 |
SIMPLE |
geo |
range |
morton |
morton |
18 |
NULL |
35976 |
Using index condition; Using where; Using temporary; Using filesort |
| 1 |
SIMPLE |
data_mediagallery |
eq_ref |
PRIMARY,vhostupload,vhostmodstatus,complexfiletype,vhostcontext,hockeynews_custom,externalcomments,vhostchannel |
PRIMARY |
4 |
sabretooth2.geo.foreignid |
1 |
Using index condition; Using where |
---
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
| geo |
CREATE TABLE `geo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vhost` bigint(20) unsigned NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`morton` bigint(20) unsigned NOT NULL,
`datatype` tinyint(3) unsigned NOT NULL,
`foreignid` bigint(20) unsigned NOT NULL,
`moderationstatus` tinyint(4) NOT NULL DEFAULT '0',
`point` point NOT NULL,
PRIMARY KEY (`id`),
KEY `morton` (`vhost`,`morton`,`moderationstatus`,`datatype`,`foreignid`),
KEY `foreignupdates` (`datatype`,`foreignid`)
) ENGINE=InnoDB AUTO_INCREMENT=1386412 DEFAULT CHARSET=utf8 |
Data_mediagallery table
| data_mediagallery |
CREATE TABLE `data_mediagallery` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`contenttype` char(40) NOT NULL DEFAULT '',
`filename` varchar(100) NOT NULL DEFAULT '',
`injector` char(40) NOT NULL DEFAULT '',
`hits` int(11) DEFAULT '0',
`message` text NOT NULL,
`date` datetime DEFAULT NULL,
`title` varchar(80) NOT NULL DEFAULT '',
`tags` varchar(255) NOT NULL DEFAULT '',
`metadata` blob,
`location` char(8) NOT NULL DEFAULT '',
`uid` int(11) unsigned NOT NULL DEFAULT '0',
`filesize` int(11) unsigned NOT NULL DEFAULT '0',
`upload` datetime DEFAULT NULL,
`privacy` tinyint(3) unsigned NOT NULL DEFAULT '0',
`width` int(10) unsigned NOT NULL DEFAULT '0',
`height` int(10) unsigned NOT NULL DEFAULT '0',
`offensive` int(10) unsigned NOT NULL DEFAULT '0',
`sourcelocation` char(8) NOT NULL DEFAULT '',
`autoblog` tinyint(1) NOT NULL DEFAULT '0',
`extension` char(10) NOT NULL DEFAULT '',
`filetype` tinyint(3) unsigned NOT NULL DEFAULT '0',
`conversiontime` float NOT NULL DEFAULT '0',
`converttime` datetime DEFAULT NULL,
`sender` varchar(100) NOT NULL DEFAULT '',
`vhost` int(10) unsigned NOT NULL DEFAULT '0',
`channel` int(10) unsigned NOT NULL DEFAULT '0',
`rotation` tinyint(3) unsigned NOT NULL DEFAULT '0',
`ofilesize` int(10) unsigned NOT NULL DEFAULT '0',
`moderationstatus` tinyint(3) unsigned NOT NULL DEFAULT '0',
`rating` decimal(8,6) DEFAULT NULL,
`votecount` int(10) unsigned NOT NULL DEFAULT '0',
`url` varchar(150) NOT NULL DEFAULT '',
`geo_latitude` double DEFAULT NULL,
`geo_longitude` double DEFAULT NULL,
`length` decimal(8,2) DEFAULT '0.00',
`parentid` int(11) NOT NULL DEFAULT '0',
`language` char(2) NOT NULL DEFAULT '',
`author` varchar(100) NOT NULL DEFAULT '',
`context` tinyint(3) unsigned NOT NULL DEFAULT '0',
`externalid` varchar(255) DEFAULT NULL,
`originalsaved` bit(1) NOT NULL DEFAULT b'1',
`hidden` tinyint(4) NOT NULL DEFAULT '0',
`commentcount` int(11) NOT NULL DEFAULT '0',
`approvedcomments` int(11) NOT NULL DEFAULT '0',
`notdeniedcomments` int(11) NOT NULL DEFAULT '0',
`lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`channelleft` int(10) unsigned NOT NULL DEFAULT '0',
`originalLocation` char(8) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `vhostupload` (`vhost`,`upload`),
KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
KEY `hockeynews_custom` (`vhost`,`channel`,`status`,`moderationstatus`,`context`),
KEY `externalcomments` (`vhost`,`moderationstatus`,`filetype`,`status`,`context`,`hidden`,`notdeniedcomments`),
KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`),
KEY `externalid` (`externalid`),
KEY `upload` (`upload`)
) ENGINE=InnoDB AUTO_INCREMENT=5740223 DEFAULT CHARSET=utf8 |
|
LPexportBug938977.xml