Query performance with join/index super slow on MariaDB 5.3.4RC

[DaveFM]

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 |

[Sergey Petrunia (sergefp)]

I've got:

MariaDB 5.2: 0.16 sec
MariaDB 5.3: 48 sec.
MariaDB 5.3, index_condition_pushdown=off: 48 sec # checked this because it was shown in EXPLAIN.

With MyISAM engine:
MariaDB 5.3: ~ 47 sec, irrespectively of index_condition_pushdown settings
MariaDB 5.2: 0.16 sec.

So, it's not the storage engine, it's not ICP.

[Sergey Petrunia (sergefp)]

userstat counter values are the same:

5.2:
+-----------------+-------------------+-----------+
| Table_schema | Table_name | Rows_read |
+-----------------+-------------------+-----------+
| bug938977myisam | data_mediagallery | 1292 |
| bug938977myisam | geo | 16137 |
+-----------------+-------------------+-----------+
2 rows in set (0.00 sec)

5.3:
+-----------------+-------------------+-----------+
| Table_schema | Table_name | Rows_read |
+-----------------+-------------------+-----------+
| bug938977myisam | data_mediagallery | 1292 |
| bug938977myisam | geo | 16137 |
| mysql | proc | 1 |
+-----------------+-------------------+-----------+

[Sergey Petrunia (sergefp)]

Parts of WHERE conditions attached to tables:

5.3:

join_tab[0]:
((geo.moderationstatus = 1) and (geo.vhost = 123) 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)))

join_tab[1]:
((data_mediagallery.vhost = 123) and (data_mediagallery.status = 3) and (data_mediagallery.moderationstatus = 1) and (data_mediagallery.hidden = 0) and (data_mediagallery.context = 1) and (data_mediagallery.channelleft between 158 and 199) and (data_mediagallery.id = geo.foreignid))

5.2:

join_tab[0]:
((geo.moderationstatus = 1) and (geo.vhost = 123) and (geo.datatype = 1) and (geo.morton between 4107809683888424684 and 4108989545352957023) and (geo.latitude between 45.99290006 and 46.44290006) and (geo.longitude between -(64.76185116) and -(64.31185116)))

join_tab[1]:
((data_mediagallery.vhost = 123) and (data_mediagallery.status = 3) and (data_mediagallery.moderationstatus = 1) and (data_mediagallery.hidden = 0) and (data_mediagallery.context = 1) and (data_mediagallery.channelleft between 158 and 199) and (data_mediagallery.id = geo.foreignid))"

[Sergey Petrunia (sergefp)]

I've added a

set @call_count=@call_count +1

statement into the stored function so I could tell how many times it is invoked. Indeed:

5.2: 6 times
5.3: 30176 times.

[Sergey Petrunia (sergefp)]

5.3 doesn't convert SP call to constant because of this piece of code in item_cmpfunc.cc:convert_const_to_int() :

  /*
    We don't need to convert an integer to an integer,
    pretend it's already converted.

    But we still convert it if it is compared with a Field_year,
    as YEAR(2) may change the value of an integer when converting it
    to an integer (say, 0 to 70).
  */
  if ((*item)->cmp_type() == INT_RESULT &&
      field_item->field_type() != MYSQL_TYPE_YEAR)
    return 1;

it was added by the cset: <email address hidden>, "merge with 5.1-micro".

[Sergey Petrunia (sergefp)]

(Note: the test dataset is on our ftp, search for file starting with this bug number)