Mariadb query gives variable performance - why and how to diagnose

Question

I have a simple table

desc house.solar;

Field	Type	Null	Default
ts	datetime	YES	NULL
measurement	varchar(15)	YES	NULL
unit	varchar(10)	YES	NULL
value	float	YES	NULL

against which I execute the following query

select  hour(ts),avg(value)
    from  solar
    where  month(ts)=4
      and  measurement = 'DP1'
      and  year(ts)=2023
    group by  hour(ts);

Sometimes the query takes a long time to execute.

I do this both from a C++/Qt 6.6.2 app and via the linux mysql command. Sometimes the query runs in "zero" time and then after a few repetitions (with different years, etc) it takes a couple of seconds. A a result the app sometimes is OK then change a parameter and it times out. I don't think I am asking a lot of the database but... any pointers as to performance issues with the query? Next any pointers as to how to diagnose the database performance?

TIA

Update

explain select hour(ts),avg(value) from house.solar where month(ts)=5 and  measurement = 'DP1' and year(ts)=2023 group by hour(ts);

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	solar	ALL	NULL	NULL	NULL	NULL	2959613	Using where; Using temporary; Using filesort

CREATE TABLE `solar` (
  `ts` datetime DEFAULT NULL,
  `measurement` varchar(15) DEFAULT NULL,
  `unit` varchar(10) DEFAULT NULL,
  `value` float DEFAULT NULL
)

when asking performance questions, please always include output of explain select ...rest of your query and show create table yourtablename (for every table used in the query) — ysth
– ysth, Commented Jun 18, 2024 at 17:59
short answer is this would benefit from having a (measurement,ts) index and rewriting the where to ts >= '2023-04-01' and ts < '2023-05-01' and measurement='DP1' — ysth
– ysth, Commented Jun 18, 2024 at 18:37
Thanks I will try that and see if it makes any difference, though I dont think it explains why the performance varies from run to run but I am guessing it is to do with the overuse of hour and year functions somehow. I will comment when I have done it but it will be a day or so due to other commitments. — waiwurrie
– waiwurrie, Commented Jun 18, 2024 at 20:29
Use ANALYZE FORMAT=JSON, or if true stuck on how it make the decision look at optimzer trace. — danblack
– danblack, Commented Jun 19, 2024 at 0:43

Rick James · Accepted Answer · 2024-07-06 22:05:43Z

0

Since unit is always the same for a given measurement, get rod of that column. (Shrinking the table helps some with speed.)
Every table should have a PRIMARY KEY; I recommend this
```
PRIMARY KEY(measurement, ts)
```

That PK, plus the following change to the WHERE should run faster:

WHERE measurement = 'DP1'
  AND ts >= '2023-04-01'
  AND ts  < '2023-04-01' + INTERVAL 1 MONTH

For further performance improvements, build and maintain a Summary Table
See also Implementation of sensor database in MySQL
MONTH() and YEAR() are not sargable .

answered Jul 6, 2024 at 22:05

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mariadb query gives variable performance - why and how to diagnose

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related