# Little help in reducing mysql query load

**URL:** <https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731>\
**Category:** Dashboard\
**Tags:** function-node, mysql\
**Created:** [25 March 2024 10:29 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731 "2024-03-25T10:29:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [25 March 2024 10:29 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/1 "2024-03-25T10:29:33Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/9/697961577eb0d8cc1214713b766491e77f96a631.png)

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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [25 March 2024 10:40 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/2 "2024-03-25T10:40:31Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [25 March 2024 11:02 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/3 "2024-03-25T11:02:54Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/f/2fa5657d6d99b33156ed3734b11bb14165a452fa.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 March 2024 11:18 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/4 "2024-03-25T11:18:41Z")

</div>

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

```sql
create procedure sp_get_results(p1...)
begin

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

end

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [25 March 2024 11:25 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/5 "2024-03-25T11:25:15Z")

</div>

> [@Steve-Mcl](#):
>
> multiple quieries in one go

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/4/c4d9d72dc0f874a84423d9ee908faac14a7d86ce.png)

> [@Steve-Mcl](#):
>
> stored procedure

will give this a try...

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [24 April 2024 11:25 UTC](https://discourse.nodered.org/t/little-help-in-reducing-mysql-query-load/86731/6 "2024-04-24T11:25:54Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.