How to increase the query or refresh time in a mysql table?

Grafana MySQL
,
1

I have a dashboard where in a graph I only consult a variable from a mysql table, I do this query in a certain time range, it is not to graph in real time since the table has almost 2 million records, for that I have temporary tables.
My question is the following in case someone knows how to solve it:
when I do a query to this table with many records where I record every second and the table has about 2 weeks of records, if the range of the query is in the first week it graphs what I ask for but if it is at the end of the second week, It is seen that the query takes a long time to be done and it does not show me anything when I have data in the table.
I attach images for better understanding.

In this first capture it looks as if it shows me the query in that requested time and date range

image
image1840×465 31.3 KB

In the second query that we are already approaching the end of the table with many records, the query keeps thinking

And it does not show anything when in the table I do have data from that requested date.

image
image1844×514 30.9 KB

Any suggestions? I understand that the problem is because the table has many records and when doing the query it takes a long time and the graph is short before reaching the data. Do you know how to extend that query time or do you have any suggestions to solve this problem?

2

You need time aggregation (avg/min/max/… - what makes sense for the use case). Example for PostgreSQL:

Without aggregation - (it may return ~2 millions records):

SELECT
  time_column AS time,
  value
FROM table
WHERE
  $__timeFilter(time_column)
GROUP BY 1
ORDER BY 1

With aggregation (it may return ~50 records):

SELECT
  $__timeGroupAlias(time_column, $__interval),
  avg(value) AS "value"
FROM table
WHERE
  $__timeFilter(time_column)
GROUP BY 1
ORDER BY 1

→ PostgreSQL calculates&returns only avg value per $__interval. Standard recommendation: read documentation of used datasource first - there are useful Grafana macros (e.g. $__timeGroupAlias in my example).

3

Thank, the solucion i work satisfactorily