Return to Question

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.

But one day longer is super low:

That's weird...

But one day longer is super slow:

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

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;

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;

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 low:

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;

That's weird...