this follow-up previous post how improve wind data sql query performance.
i have expanded sql statement perform first part in calculation of average wind direction using circular statistics. means want calculate average of cosines , sines of wind direction. in php script, perform second part , calculate inverse tangent , add 180 or 360 degrees if necessary.
the wind direction stored in table voltages read sensor in field 'dirvolt' first need convert radians.
the user can @ historical wind data stepping backwards using pagination function, hence use of limit values set dynamically in php script.
my sql statement looks this:
select round(avg(speed),1) speed_mean, max(speed) speed_max, min(speed) speed_min, max(dt) last_dt, avg(sin(2.04*dirvolt-0.12)) dir_sin_mean, avg(cos(2.04*dirvolt-0.12)) dir_cos_mean table group floor(unix_timestamp(dt) / 300) order floor(unix_timestamp(dt) / 300) desc limit 0, 72 the query takes 3-8 seconds run depending on value use group data (300 in code above).
in order me learn, there can optimize or improve sql statement otherwise?
show create table table; from can see if have index(dt) (or equivalent). that, can modify select faster.
but first, change focus 72*300 seconds worth of readings datetime ranges, 6(?) hours.
let's @ query:
select * table dt >= '...' - interval 6 hour , dt < '...'; the '...' same datetime in both places. run fast enough index?
if yes, let's build final query using subquery:
select format(avg(speed), 1) speed_mean, max(speed) speed_max, min(speed) speed_min, max(dt) last_dt, avg(sin(2.04*dirvolt-0.12)) dir_sin_mean, avg(cos(2.04*dirvolt-0.12)) dir_cos_mean ( select * table dt >= '...' - interval 6 hour , dt < '...' ) x group floor(unix_timestamp(dt) / 300) order floor(unix_timestamp(dt) / 300) desc; explanation: had not use index, hence had scan entire table (which getting bigger , bigger). subquery use index, hence faster. effort outer query not "too bad" since worked n rows.
