MySql query consuming more time to display data in Grafana dashboard

Grafana MySQL
1

I am using MySQL as a data source in grafana dashboard and using MySQL query with joins and group by clause.

select stu.name,(count(case when r.status='PASS' Then 'Pass' end) / count(*)) * 100 as pass_percentage from student stu inner join result r on stu.student_id=r.student_id inner join subjects sub on sub.subject_id=stu.subject_id and sub.subject_name is not null group by stu.id;

This query takes less than 3 seconds in MySQL workbench but taking more time like 5 to 7 minutes on grafana dashboard.

MySQL Version - 8.0.20
Grafana Version - 7.3.1

How can I improve the performance of the MySQL query.

2

This is expected, because in the MySQL Workbench have limit of the result in the default configuration and then you can see the next result by click next.
In Grafana, if you don’t have time range filter, then grafana will get whole rows of the query.
So, I suggest 2 things:

  1. Add column named as “time” from the timestamp field
  2. Add $__timeFilter(timestamp) in the WHERE clause

$__timeFilter(timestamp) relate to the Time Range interval that on the top right of the dashboard page

3

Thank you @fadjar340, even after adding timestamp field still MySQL query has taken more time.

4

Is the Mysql Workbench in the same server with the grafana? If not, please try using mysql client in grafana server and compare it with grafana result…
Perhaps, the network can cause the delay…