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!