I'm trying to show a table, for each ID, to see if there is data available. In this case, its measurements. If there's no data, that means the logger isn't doing its job properly.

I currently have two tables: `data` and `times`. `data` contains `id`, `datetime`, `sensor_id`, and `value`. `times` contains `id` and `value`. 

Times is filled with `00:00`, `00:01`, `00:02`, etc., all the way through to `23:59`. 

I have this query:

```
SELECT t.`value`, d.`sensor_id`, COUNT(t.`value`) as `numrows`
FROM `data` d
RIGHT JOIN `times` t
ON d.`datetime` LIKE CONCAT('% ', t.`value`, '%')
WHERE d.`datetime` LIKE '%$2014-11-05%'
AND d.`sensor_id` IN(1,2,3,4,5,999)
GROUP BY d.`sensor_id`, t.`value`
ORDER BY d.`sensor_id` ASC, t.`id` ASC

while($s = $select->fetch_assoc()) {
    $checkArray[$s['sensor_id']][$s['value']] = $s['numrows'];
}

foreach($checkArray as $key => $arr) {
    echo 'Sensor: ' . $key;
    for($i = 0; $i <= 23; $i++) {
        for($j = 0; $j <= 59; $j++) {
            $time = strlen($i) == 1 ? '0' . $i : '' . $i;
            $time .= ':';
            $time .= strlen($j) == 1 ? '0' .$j : '' . $j;

            if(isset($arr[$time]) && $arr[$time] >= 1) { //See if has at least one row
                echo 'YES DATA FOR ' . $time . '<br>';
            }
        }
    }
}

```

Of course, I sort this in a table, and the result is this:

![Result](https://i.imgur.com/LoDI8tQ.png "Result")

Just for sensors 1, 2, 3, 4, and 5, the load time is over 5.5 seconds. I don't know how to optimize this further. I've put indexes on the queried columns, but I can't think of anything else. 

My `SHOW CREATE TABLE` for `data`: 

```
CREATE TABLE `data` (
 `id` int(13) NOT NULL AUTO_INCREMENT,
 `sensor_id` int(13) NOT NULL,
 `datetime` datetime NOT NULL,
 `value` float NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `sensor_id_2` (`sensor_id`,`datetime`,`value`),
 KEY `sensor_id` (`sensor_id`),
 KEY `value` (`value`),
 KEY `datetime` (`datetime`),
 KEY `sensor_id_3` (`sensor_id`),
 KEY `datetime_2` (`datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=103921 DEFAULT CHARSET=utf8

```

And my `SHOW CREATE TABLE` for `times`:

```
CREATE TABLE `minutes` (
 `id` int(13) NOT NULL AUTO_INCREMENT,
 `value` varchar(16) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_3` (`id`),
 KEY `value` (`value`),
 KEY `id` (`id`),
 KEY `value_2` (`value`),
 KEY `id_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1441 DEFAULT CHARSET=utf8

```