Public Dashboards: Workarounds for Variable Limitations in MySQL Queries?

Grafana Dashboards
1

I have a complex MySQL query that I cannot optimize further, so I had to split it into 3 queries using variables. However, I want to share this dashboard as a public dashboard - unfortunately that does not work with variables…

Example
Variable 1 - locationIds: Get all location IDs for customer
Variable 2 - transactionIds: Get all transaction IDs related to above location IDs
Panel: Using transactionIds, run some query on it and present the result.

I have tried:

  • Optimizing the overall query (all 3 in one) - no success :x:
  • Using multiple MySQL statements and variables - does not work in Grafana :x:

I am looking for ideas to work around this limitation for public dashboards… anything to cache variable1/2 results (without using variables) and allowing me to reference to it in the Panel Query. Is there any Plug-In that could help?

Thanks!

2

can you use temp tables for each

CREATE TEMPORARY TABLE IF NOT EXISTS locationIds AS (SELECT * FROM table1)

CREATE TEMPORARY TABLE IF NOT EXISTS transactionIdsAS (SELECT * FROM table2)

select *
 from mainOne mo
  join locationIds li on mo.locationId = li.locationId
  join transactionIds ti on mo.transactionId = ti.transactionId