New answers tagged

0

(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. For this: AND (NOT (`cs`.`...


1

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) ); Fiddle Let's see if your query can be reformulated (I changed it ...


2

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) FROM plant_data WHERE created_at >= '2020-02-15 00:00:00' AND created_at <= '2020-02-17 00:00:00' GROUP BY plant_id, date(...


1

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 ...


1

One more way: GRANT PROCESS, SELECT ON *.* TO dynatrace;


1

From the MariaDB documentation: To set a global privilege, use *.* for priv_level. So your command should be GRANT PROCESS ON *.* TO dynatrace;


Top 50 recent answers are included