MySQL query efficiency and general MySQL dashboard questions

Hey everyone, a little background info… I’m pretty new to MySQL in general, but I’ve been asked to make some time series graphs of some data coming in from some sensors. My question is essentially, is there a certain way I should be writing my queries in order to promote good health of the interface (fast update, good performance, no crashes, etc)? I’ve had the server crash once or twice… The general query format I’m using looks as follows:

SELECT
UNIX_TIMESTAMP(<time column>) AS time,
<data column> AS value,
CASE
	WHEN (device_type=1) THEN 'Case 1'
	WHEN (device_type=2) THEN 'Case 2'
END AS metric
FROM <table name>
WHERE ((<time column> > NOW() - INTERVAL 7 DAY) AND (device_type=1 OR device_type=2))
ORDER BY <time column>;

First question is, if I use this to query the last 7 days, and I leave this running every so often for the next month, will it log the previous data or continue to update only the exact past 7 days? I would like to only have to query a small section in order to increase performance, but also store all previous data…

Second question, Is this considered an inefficient query? Within MySQL Workbench it says it only takes 0.266s/0.110 for duration/fetch… Being new to MySQL and SQL in general, I’m not sure if that is fast or slow, or how to speed it up if need be, but compared to other queries, the fetch especially seems long… I mainly want to be able to just create a consistent interface that doesn’t have lag time as it does now. Any help or suggestions would be great!

I’m no expert on MySQL, but going to try and give some answers/pointers.

First of all you should almost always use the __timeFilter macro function so that the time range selected in dashboard are used to filter the data in where-clause. Then you'll select last 7 days in time picker or panel time override instead of hard coding it in the query. Would recommend to use the latest version of Grafana since there have been some performance optimizations regarding the __timeFilter macro recently.

No, only the last 7 days.

Hard to tell, but you probably want to add some indices that’s “optimal” for your query patterns. There’s probably a lot of resources on this topic elsewhere, search and you should find. For example having an ordered index of the time column would allow the order by clause to just read straight from the index instead of ordering it on the fly.

Question is if the query is considered efficient or inefficient given the result returned to Grafana UI. If you return to many data points the browser may crash. In scenarios where you have a lot of data points I would suggest to use the $__timeGroup macro to group/aggregate the result over time and/or adjust the time range you’re looking at.

The best suggestion I can give you is to start evaluating and optimize when problem arise, if so. Eventually it could also be that MySQL is not suitable/performant enough for the data you’re trying to visualize and that’s where you should start looking at specific timeseries databases.

Refer to documentation for further information.

Marcus

1 Like