SELECT
T1. Device_id
The FROM
T_device t1
INNER JOIN
T_online t3
ON t1. Device_id=t3. Device_id
LEFT the JOIN
(
SELECT
Spyware doctor evice_id,
S.a vg_tdc
The FROM
T_site_houravg_tdc s
WHERE
S.c reate_time & gt; DATE_ADD (NOW (), the INTERVAL 1 HOUR)
) AS t2
ON t1. Device_id=t2. Device_id
WHERE
T1. The customer_id='1'
AND t1. ` status `=1
AND t1. The type_id=1539730699168001
AND (
T2. Avg_tdc & gt; 9980
The OR t2. Avg_tdc IS NULL
)

CodePudding user response:

Directly to create a view, and then a query view can promote efficiency

CodePudding user response:

Don't know you list the amount of data, data types, index, can only change it roughly,
SELECT
T1. Device_id
The FROM
T_device t1
INNER JOIN
T_online t3
ON t3. Device_id=t1. Device_id
LEFT the JOIN
T_site_houravg_tdc t2
ON t2. Device_id=t1. Device_id and t2, create_time & gt; DATE_ADD (NOW (), the INTERVAL 1 HOUR)
WHERE
T1. The customer_id='1'
AND t1. ` status `=1
AND t1. The type_id=1539730699168001
AND (
T2. Avg_tdc & gt; 9980
The OR t2. Avg_tdc IS NULL
)

CodePudding user response:

refer to the second floor AHUA1001 response:

don't know you list the amount of data, data types, index, can only change it roughly,
SELECT
T1. Device_id
The FROM
T_device t1
INNER JOIN
T_online t3
ON t3. Device_id=t1. Device_id
LEFT the JOIN
T_site_houravg_tdc t2
ON t2. Device_id=t1. Device_id and t2, create_time & gt; DATE_ADD (NOW (), the INTERVAL 1 HOUR)
WHERE
T1. The customer_id='1'
AND t1. ` status `=1
AND t1. The type_id=1539730699168001
AND (
T2. Avg_tdc & gt; 9980
The OR t2. Avg_tdc IS NULL
)

Thank you, execution plan did not change, perform no optimization

CodePudding user response:

T2 is a temporary table there is an index, slow, of course, can use the new version of mysql, or don't have left the join, or optimize again,