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.