Little help in reducing mysql query load

[smanjunath211]

I am using mysql query to get the data from a database every minute for last 60 minutes. now to update just the last minute data, i am querying again full 60 datapoints every minute (limit 60). is there a way to just take the last minute data (limit 1) and keep the old data and shift it by one minute in the display so the 60th data goes out and 1st minute comes in (second from last moves to last etc) sorry unable to word my question better than this.

may be a picture would help.

in the above picture, 306 (at 14:59) would move out of screen, and latest data of 15:59 would appear in the first line, and every data moves one station.

[jbudd]

My instinct is to offload work from Node-red into SQL in the naive expectation that an SQL query, however ugly, is probably more efficient than a Node-red flow.
More, I suspect that the processing demands of SELECT ... LIMIT 1 and LIMIT 60 are nearly identical.

But you could hold the values in an array and unshift to insert the latest value at the beginning, pop to remove the last value.

Do you have an actual mySQL performance problem or is this just theoretical?

[smanjunath211]

@jbudd

are nearly identical.

Hmmm... yes you are right, the time taken for both the queries are almost same.

since you asked this, i checked my query again, it is a set of queries actually, so i am querying not one, but 5 queries (each one for 15 minutes bucket)
..limit 0,15)
..limit 15,15)
..limit 30,15)
..limit 45,15) and one for total 60 ..limit 0,60
so in all there are 6 queries, hence it is taking so long.

need to tidy up the query., take one result and split outside

let me work on this,

thanks for the hint.

[Steve-Mcl]

Have you tried multiple quieries in one go?

It works for MSSQL-plus nodes (have never tried with MySQL)

Alternatively, create a stored procedure that returns multiple queries

create procedure sp_get_results(p1...)
begin

select x, y from table1 where p1...;
select x, y from table2 where p2...;

end

[smanjunath211]

@Steve-Mcl

multiple quieries in one go

if you mean like below, that is how i have set up.

@Steve-Mcl

stored procedure

will give this a try...