0

I have the following query:

SELECT ld.idDataAttribute AS idDataAttribute, ld.instance AS instance 
FROM logs l 
INNER JOIN logData ld ON l.idLog = ld.idLog 
INNER JOIN dataAttributes da ON ld.idDataAttribute = da.idDataAttribute
WHERE l.idSite = 2776 
AND l.timestamp >= 1430438400 
AND l.timestamp <= 1433116800 limit 100;

On our VM with MySQL 5.5 this is fast. On our Amazon RDS (MySQL 5.6, db.m3.large, 1500GB, 4500 IOPS on SSD), which is a copy of the first DB, this is very slow. It also causes a CPU spike, that is not visible on the MySQL 5.5 server. The Amazon RDS instance takes more time than I have patience, so I don't know how long it runs. The original server takes a few seconds on the first run, and is instant later.

The EXPLAIN is also different. On the old MySQL 5.5 VM:

+----+-------------+-------+-------+------------------------+--------------+---------+--------------------------+-------+--------------------------------+
| id | select_type | table | type  | possible_keys          | key          | key_len | ref                      | rows  | Extra                          |
+----+-------------+-------+-------+------------------------+--------------+---------+--------------------------+-------+--------------------------------+
|  1 | SIMPLE      | l     | range | PRIMARY,idSite         | idSite       | 9       | NULL                     | 18354 | Using where; Using index       |
|  1 | SIMPLE      | da    | index | PRIMARY                | idDeviceType | 6       | NULL                     |     3 | Using index; Using join buffer |
|  1 | SIMPLE      | ld    | ref   | PRIMARY,logdata_ibfk_4 | PRIMARY      | 8       | victron_vrm_2012.l.idLog |    19 | Using where; Using index       |
+----+-------------+-------+-------+------------------------+--------------+---------+--------------------------+-------+--------------------------------+

And on Amazon:

+----+-------------+-------+--------+------------------------+----------------+---------+-------------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key            | key_len | ref                                 | rows | Extra       |
+----+-------------+-------+--------+------------------------+----------------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | da    | index  | PRIMARY                | idDeviceType   | 6       | NULL                                |  248 | Using index |
|  1 | SIMPLE      | ld    | ref    | PRIMARY,logdata_ibfk_4 | logdata_ibfk_4 | 2       | victron_vrm_2012.da.idDataAttribute |  424 | Using index |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,idSite         | PRIMARY        | 8       | victron_vrm_2012.ld.idLog           |    1 | Using where |
+----+-------------+-------+--------+------------------------+----------------+---------+-------------------------------------+------+-------------+

The Amazon result lacks the using where and using join buffer. Also Amazon uses the eq_ref, which should be better, right?

Is there even anything I can deduce from this EXPLAIN?

edit:

very weird. This is very fast:

SELECT ld.idDataAttribute AS idDataAttribute, ld.instance AS instance
FROM logs l 
INNER JOIN logData ld ON l.idLog = ld.idLog 
INNER JOIN dataAttributes da ON ld.idDataAttribute = da.idDataAttribute
WHERE l.idSite = 2776 
AND l.timestamp >= 1430438400 
AND l.timestamp <= unix_timestamp('2015-05-23') 
limit 100;

But one day longer is super slow:

SELECT ld.idDataAttribute AS idDataAttribute, ld.instance AS instance
FROM logs l 
INNER JOIN logData ld ON l.idLog = ld.idLog 
INNER JOIN dataAttributes da ON ld.idDataAttribute = da.idDataAttribute
WHERE l.idSite = 2776 
AND l.timestamp >= 1430438400 
AND l.timestamp <= unix_timestamp('2015-05-24') 
limit 100;

The EXPLAIN of the may 23rd one (the fast one), also shows that now it does use Using where; Using index.... That seems to be a deciding factor.

Tables:

CREATE TABLE `logs` (
  `idLog` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `idSite` int(10) unsigned NOT NULL,
  `secondsToNextLog` int(11) DEFAULT NULL 
  `gwRestarted` tinyint(4) NOT NULL 
  `timestamp` int(10) unsigned DEFAULT NULL 
  PRIMARY KEY (`idLog`),
  KEY `idSite` (`idSite`,`timestamp`),
  CONSTRAINT `logs_ibfk_1` FOREIGN KEY (`idSite`) REFERENCES `sites` (`idSite`)
) ENGINE=InnoDB AUTO_INCREMENT=380946254 DEFAULT CHARSET=latin1

CREATE TABLE `logData` (
  `idLog` bigint(20) unsigned NOT NULL,
  `instance` smallint(5) unsigned NOT NULL 
  `idDataAttribute` smallint(5) unsigned NOT NULL,
  `valueFloat` float DEFAULT NULL,
  `valueString` varchar(255) DEFAULT NULL,
  `valueEnum` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`idLog`,`instance`,`idDataAttribute`),
  KEY `logdata_ibfk_4` (`idDataAttribute`),
  CONSTRAINT `logData_ibfk_3` FOREIGN KEY (`idLog`) REFERENCES `logs` (`idLog`) ON DELETE CASCADE,
  CONSTRAINT `logData_ibfk_4` FOREIGN KEY (`idDataAttribute`) REFERENCES `dataAttributes` (`idDataAttribute`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `dataAttributes` (
  `idDataAttribute` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `idDeviceType` int(10) unsigned NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `dataType` enum('float','string','enum') COLLATE utf8_unicode_ci NOT NULL,
  `sortOrder` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exportType` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `formatValueOnly` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `formatWithUnit` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`idDataAttribute`),
  UNIQUE KEY `code_UNIQUE` (`code`),
  KEY `idDeviceType` (`idDeviceType`,`idDataAttribute`),
  CONSTRAINT `dataAttributes_ibfk_1` FOREIGN KEY (`idDeviceType`) REFERENCES `deviceTypes` (`idDeviceType`)
) ENGINE=InnoDB AUTO_INCREMENT=251 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Another edit: I've discovered that with a different range, it decides to use a different index. When I use force index(idSite), then it does work.

  • Show us the SHOW CREATE TABLE logs; (for all 3 tables) Also run these in both servers and compare them to make sure there is absolutely no difference. – ypercubeᵀᴹ Jun 15 '15 at 18:08
  • There are several optimizer enhancements from 5.6 so I would not expect the same results across the two versions necessarily. – LowlyDBA Jun 15 '15 at 18:17
  • But I've now also gotten significant results just by changing the date range by one. See bottom of the post. – Halfgaar Jun 15 '15 at 18:22
  • 1
    "in the real query I need the join" If you want help for your real query, provide it. Removing essential info from your question, does not help others to help you. – ypercubeᵀᴹ Jun 15 '15 at 20:57
  • 1
    It would be worth to try using ORDER BY l.idSite, l.timestamp and LEFT JOIN dataAttributes da instead of inner join (only for that join). It might convince mysql to use the wanted index. Of course, you may need the inner join for your "real" query, and all bets are off ... – ypercubeᵀᴹ Jun 15 '15 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.