(I need formatting not available in a Comment.)
DATE(...) is not sargable. There is probably no reason to use the DATE function; simply do
cs.created_at BETWEEN cm.date_range_from AND cm.date_range_to
But, that won't really help -- comparing something to a range computed from other columns cannot be optimized.
AND (NOT (`cs`.`...
The problem is that the IF statement cannot contain a set, it needs to be a scalar. Let's illustrate with:
create table T (x int not null);
insert into T (x) values (1),(2);
-- invalid since `SELECT x FROM T` is a set of rows
SELECT * FROM T WHERE 1 IN ( IF (1=1, SELECT x FROM T, 1) );
Let's see if your query can be reformulated (I changed it ...
First, a very nice first post. Both ddl and sample data makes it easy to recreate the setup. Second, all you have to do with your query is to add the date dimension:
SELECT plant_id, date(created_at) as day, avg(temperature)
WHERE created_at >= '2020-02-15 00:00:00'
AND created_at <= '2020-02-17 00:00:00'
GROUP BY plant_id, date(...
For general monitoring, this comes closer to covering all bases, but without being able to "cause damage":
GRANT SELECT, PROCESS, REFERENCES, SHOW DATABASES,
REPLICATION CLIENT, SHOW VIEW
ON *.* TO username@'...';
The ON *.* is necessary (though clumsy) syntax.
If the data is sensitive, remove SELECT. It can even be argued that being able to ...