So, I've been given the task of replicating functionality we currently handle via code, within MySQL. 

The query below works beautifully, bringing back 245,000 rows in 40ms, however as soon as you touch it with a group or order, it takes over 6s. 

Does anyone have any suggestions on what changes need making to the indexes or potentially how to modify the query to improve it?

Thanks

---

Without any grouping or ordering
--------------------------------

```
select 
    s.id as sensorid,
    s.sensortypeid,
    COALESCE(s.pulserate, 1) as pulserate,
    COALESCE(s.correctionFactor, 1) as correctionFactor,

    ur.id as unitrateid,
    COALESCE(ur.priceperkwh, 0) as priceperkwh,
    COALESCE(ur.duosCharges, 0) as duosCharges,

    IF(t.blnnonunitratecharges, t.nonunitratecharge/48, 0) as nonunitratecost,
    IF(t.blnFeedIn, COALESCE(t.feedInRate, 0), 0) as feedInRate,
    IF(t.blnRoc, COALESCE(t.rocRate, 0), 0) as rocRate,

    from_unixtime(FLOOR(UNIX_TIMESTAMP(srs.dateTimeStamp)/(30*60))*(30*60)) as timeKey

from sensorreadings srs
    inner join sensorpoints sp on (sp.id = srs.sensorpointid)
    inner join sensors s on (s.id = sp.sensorid)
    left join unitrates ur on ur.id = (
        select 
            ur.id 
        from unitrates ur, tariffs t, companyhubs ch
        where 
            ur.tariffid = t.id and
            t.companyid = ch.companyid and 
            ch.hubid = s.hubid and 
            t.utilitytypeid = s.utilitytypeid and 
            (srs.dateTimeStamp between t.startdate and t.enddate) and 
            ((time(srs.dateTimeStamp) between ur.starttime and ur.endtime) and 
            (ur.dayMask & POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask & POW(2, MONTH(srs.dateTimeStamp) - 1) <> 0)) 
        order by 
            t.startdate desc, 
            ur.starttime desc 
        limit 0, 1
    )
    left join tariffs t on (t.id = ur.tariffid)
where 
    s.id = 5289

```

With grouping and ordering
--------------------------

```
select 
    s.id as sensorid,
    s.sensortypeid,
    COALESCE(s.pulserate, 1) as pulserate,
    COALESCE(s.correctionFactor, 1) as correctionFactor,

    ur.id as unitrateid,
    COALESCE(ur.priceperkwh, 0) as priceperkwh,
    COALESCE(ur.duosCharges, 0) as duosCharges,

    IF(t.blnnonunitratecharges, t.nonunitratecharge/48, 0) as nonunitratecost,
    IF(t.blnFeedIn, COALESCE(t.feedInRate, 0), 0) as feedInRate,
    IF(t.blnRoc, COALESCE(t.rocRate, 0), 0) as rocRate,

    min(srs.reading) as minReading,
    avg(srs.reading) as avgReading,

    from_unixtime(FLOOR(UNIX_TIMESTAMP(srs.dateTimeStamp)/(30*60))*(30*60)) as timeKey

from sensorreadings srs
    inner join sensorpoints sp on (sp.id = srs.sensorpointid)
    inner join sensors s on (s.id = sp.sensorid)
    left join unitrates ur on ur.id = (
        select 
            ur.id 
        from unitrates ur, tariffs t, companyhubs ch
        where 
            ur.tariffid = t.id and
            t.companyid = ch.companyid and 
            ch.hubid = s.hubid and 
            t.utilitytypeid = s.utilitytypeid and 
            (srs.dateTimeStamp between t.startdate and t.enddate) and 
            ((time(srs.dateTimeStamp) between ur.starttime and ur.endtime) and 
            (ur.dayMask & POW(2, WEEKDAY(srs.dateTimeStamp)) <> 0) and 
            (ur.monthMask & POW(2, MONTH(srs.dateTimeStamp) - 1) <> 0)) 
        order by 
            t.startdate desc, 
            ur.starttime desc 
        limit 0, 1
    )
    left join tariffs t on (t.id = ur.tariffid)
where 
    s.id = 5289
group by timeKey
order by timeKey desc

```

**Schemas**

```
CREATE TABLE `sensorreadings` (
    `sensorpointid` int(11) NOT NULL DEFAULT '0',
    `reading` decimal(15,5) NOT NULL,
    `dateTimeStamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`sensorpointid`,`dateTimeStamp`),
    KEY `sensormetricid` (`sensormetricid`),
    KEY `sensorreadings_timestamp` (`dateTimeStamp`,`sensorpointid`),
    KEY `sensorpointid` (`sensorpointid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sensorpoints` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sensorid` int(11) DEFAULT NULL,
    `hubpointid` int(11) DEFAULT NULL,
    `pointlabel` varchar(255) NOT NULL,
    `pointhash` varchar(255) NOT NULL,
    `target` decimal(10,0) DEFAULT NULL,
    `tolerance` decimal(10,0) DEFAULT '0',
    `blnlivepoint` int(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `FK_sensorpoints_sensors` (`sensorid`),
    CONSTRAINT `FK_sensorpoints_sensors` FOREIGN KEY (`sensorid`) REFERENCES `sensors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8824 DEFAULT CHARSET=latin1;

CREATE TABLE `sensors` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `hubid` int(11) DEFAULT NULL,
    `sensortypeid` int(11) NOT NULL DEFAULT '5',
    `pulserate` decimal(10,6) DEFAULT NULL,
    `utilitytypeid` int(11) NOT NULL DEFAULT '1',
    `correctionfactor` decimal(10,3) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_sensors_sensortypes` (`sensortypeid`),
    KEY `FK_sensors_hubs` (`hubid`),
    KEY `FK_sensors_utilitytypes` (`utilitytypeid`),
    CONSTRAINT `FK_sensors_hubs` FOREIGN KEY (`hubid`) REFERENCES `hubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_sensors_sensortypes` FOREIGN KEY (`sensortypeid`) REFERENCES `sensortypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=5503 DEFAULT CHARSET=latin1;

CREATE TABLE `tariffs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `companyid` int(11) DEFAULT NULL,
    `utilitytypeid` int(11) DEFAULT NULL,
    `startdate` date NOT NULL,
    `enddate` date NOT NULL,
    `blnnonunitratecharges` int(1) DEFAULT '0',
    `nonunitratecharge` decimal(16,8) DEFAULT '0.00000000',
    `blnFeedIn` int(1) DEFAULT '0',
    `blnRoc` int(1) DEFAULT '0',
    `rocRate` decimal(16,8) DEFAULT '0.00000000',
    `feedInRate` decimal(16,8) DEFAULT '0.00000000',
    PRIMARY KEY (`id`),
    KEY `companyid` (`companyid`,`utilitytypeid`,`startdate`,`enddate`),
    KEY `startdate` (`startdate`,`enddate`),
) ENGINE=InnoDB AUTO_INCREMENT=1107 DEFAULT CHARSET=latin1;

CREATE TABLE `unitrates` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `tariffid` int(11) NOT NULL,
    `priceperkwh` decimal(16,8) NOT NULL,
    `starttime` time NOT NULL,
    `endtime` time NOT NULL,
    `duoscharges` decimal(10,5) DEFAULT NULL,
    `dayMask` int(11) DEFAULT '127',
    `monthMask` int(11) DEFAULT '4095',
    PRIMARY KEY (`id`),
    KEY `FK_unitrates_tariffs` (`tariffid`),
    KEY `times` (`starttime`,`endtime`),
    KEY `masks` (`dayMask`,`monthMask`),
    CONSTRAINT `FK_unitrates_tariffs` FOREIGN KEY (`tariffid`) REFERENCES `tariffs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=3104 DEFAULT CHARSET=latin1;

```

**Explains**

*Without groups/ordering*

```
| id | select_type        | table | type   | possible_keys                   | key                     | key_len | ref                           | rows | Extra                                        | 
|----|--------------------|-------|--------|---------------------------------|-------------------------|---------|-------------------------------|------|----------------------------------------------| 
| 1  | PRIMARY            | s     | const  | PRIMARY                         | PRIMARY                 | 4       | const                         | 1    | NULL                                         | 
| 1  | PRIMARY            | sp    | ref    | PRIMARY,FK_sensorpoints_sensors | FK_sensorpoints_sensors | 5       | const                         | 1    | Using index                                  | 
| 1  | PRIMARY            | srs   | ref    | PRIMARY,sensorpointid           | PRIMARY                 | 4       | dbnameprod.sp.id              | 211  | Using index                                  | 
| 1  | PRIMARY            | ur    | eq_ref | PRIMARY                         | PRIMARY                 | 4       | func                          | 1    | Using where                                  | 
| 1  | PRIMARY            | t     | eq_ref | PRIMARY                         | PRIMARY                 | 4       | dbnameprod.ur.tariffid        | 1    | NULL                                         | 
| 2  | DEPENDENT SUBQUERY | ch    | ref    | hubid                           | hubid                   | 5       | const                         | 1    | Using where; Using temporary; Using filesort | 
| 2  | DEPENDENT SUBQUERY | t     | ref    | PRIMARY,companyid,startdate     | companyid               | 10      | dbnameprod.ch.companyid,const | 1    | Using where; Using index                     | 
| 2  | DEPENDENT SUBQUERY | ur    | ref    | FK_unitrates_tariffs,times      | FK_unitrates_tariffs    | 4       | dbnameprod.t.id               | 1    | Using where                                  | 

```

*With ordering/grouping*

```
| id | select_type        | table | type   | possible_keys                                                 | key                     | key_len | ref                           | rows | Extra                                        | 
|----|--------------------|-------|--------|---------------------------------------------------------------|-------------------------|---------|-------------------------------|------|----------------------------------------------| 
| 1  | PRIMARY            | s     | const  | PRIMARY                                                       | PRIMARY                 | 4       | const                         | 1    | Using temporary; Using filesort              | 
| 1  | PRIMARY            | sp    | ref    | PRIMARY,FK_sensorpoints_sensors                               | FK_sensorpoints_sensors | 5       | const                         | 1    | Using index                                  | 
| 1  | PRIMARY            | srs   | ref    | PRIMARY,sensormetricid,sensorreadings_timestamp,sensorpointid | PRIMARY                 | 4       | dbnameprod.sp.id              | 211  | Using index                                  | 
| 1  | PRIMARY            | ur    | eq_ref | PRIMARY                                                       | PRIMARY                 | 4       | func                          | 1    | Using where                                  | 
| 1  | PRIMARY            | t     | eq_ref | PRIMARY                                                       | PRIMARY                 | 4       | dbnameprod.ur.tariffid        | 1    | NULL                                         | 
| 2  | DEPENDENT SUBQUERY | ch    | ref    | hubid                                                         | hubid                   | 5       | const                         | 1    | Using where; Using temporary; Using filesort | 
| 2  | DEPENDENT SUBQUERY | t     | ref    | PRIMARY,companyid,startdate                                   | companyid               | 10      | dbnameprod.ch.companyid,const | 1    | Using where; Using index                     | 
| 2  | DEPENDENT SUBQUERY | ur    | ref    | FK_unitrates_tariffs,times                                    | FK_unitrates_tariffs    | 4       | dbnameprod.t.id               | 1    | Using where                                  | 

```