How to optimize this table? Query good slow now

CodePudding user response:

CodePudding user response:

With text posted ( don't map!
)
Show the create table...

the explain the select...
And
show index from

For analysis

CodePudding user response:

You too much calculation in the SQL, SQL is the best accurate query, conditions more it doesn't matter, but the operation will be serious delay query efficiency, can try to parts of the operation in the code

CodePudding user response:

reference 1st floor Lcindep110 response:

refer to the second floor ACMAIN_CHM response:

text way posted ( don't map!
)
Show the create table...

the explain the select...
And
show index from

For analysis

good

CodePudding user response:

Show the index from jh_device_upload_5387

Jh_device_upload_5387 0 PRIMARY id - A 1 45901 BTREE
Jh_device_upload_5387 0 2 terminal A PRIMARY 45901 BTREE
Jh_device_upload_5387 0 PRIMARY 3 infotypes A 45901 BTREE
Jh_device_upload_5387 newFinishMonth 1 newFinishMonth A 2 YES BTREE
Jh_device_upload_5387 newFinishDate 1 newFinishDate A 108 YES BTREE
Jh_device_upload_5387 newFinishHour 1 newFinishHour A 1207 YES BTREE
Jh_device_upload_5387 newFinishMinute 1 newFinishMinute A 45901 YES BTREE
Jh_device_upload_5387 terminal_newFinishMinute 1 terminal 2 A BTREE
Jh_device_upload_5387 1 45901 YES terminal_newFinishMinute 2 newFinishMinute A BTREE

CodePudding user response:

The EXPLAIN

The select terminal,
Avg (fun_get_value (substring (, filedir,, 113))) as noise,
Avg (fun_get_value (substring (, filedir,, 129))) as pm25,
Avg (fun_get_value (substring (, filedir,, 145))) as pm10,
Avg (fun_get_value (substring (, filedir,, 161))) as air temperature,
Avg (fun_get_value (substring (, filedir,, 177))) as humidity,
Avg (fun_get_value (substring (, filedir,, 193))) as windspeed,
Avg (fun_get_value (substring (, filedir,, 225))) as the TSP, newFinishHour as d
The from jh_device_upload_5387 FORCE INDEX (terminal_newFinishMinute)
Where terminal=5387 and
NewFinishMinute>=201708210000 and
NewFinishMinute<=201709211459
Group by terminal, d


1 SIMPLE jh_device_upload_5387 range terminal_newFinishMinute terminal_newFinishMinute 13 22956 Using the where; Using temporary; Using filesort

CodePudding user response:

To calculate operation, first check

CodePudding user response:

The create index on jh_device_upload_5387 XXX (terminal, newFinishHour)

CodePudding user response:

Estimation is the problem that a custom function, you can try to get rid of function, only do the query efficiency