After the union limit lead to slow-CodePudding

 
EXPLAIN the SELECT 
The mileage, 
Departurecode, 
Destinationcode, 
Departurelescode, 
Departurelesname, 
Destinationlescode, 
Destination_name, 
Center_code, 
Rrs_create_date, 
Rrs_create_by, 
Id, 
Operate, 
The STATUS 
The FROM 
(
SELECT 
A1. Mileage, 
A1. Departurecode, 
A1. Destinationcode, 
A1. Departurelescode, 
A1. Departurelesname, 
A1. Destinationlescode, 
A1. Destination_name, 
A1. Center_code, 
A1. Rrs_create_date, 
A1. Rrs_create_by, 
A1. Std_id AS id, 
Md. Operate, 
F. ` status ` 
The FROM 
Md_lczsjm_record a1 
LEFT the JOIN maintain_detail md ON md. Pre_merge_id=a1. Id 
LEFT the JOIN maintain m ON m.i d=md parent_id 
LEFT the JOIN flows ON flow_id f=f.i d 
WHERE 
Order_no & gt; 0 
IS NULL AND f.i d 
The UNION 
SELECT 
A1. Mileage, 
A1. Departurecode, 
A1. Destinationcode, 
A1. Departurelescode, 
A1. Departurelesname, 
A1. Destinationlescode, 
A1. Destination_name, 
A1. Center_code, 
A1. Rrs_create_date, 
A1. Rrs_create_by, 
A1. Std_id AS id, 
Md. Operate, 
F. ` status ` 
The FROM 
Md_lczsjm_record a1 
LEFT the JOIN maintain_detail md ON md. Pre_merge_id=a1. Id 
LEFT the JOIN maintain m ON m.i d=md parent_id 
LEFT the JOIN flows ON flow_id f=f.i d 
WHERE 
Order_no & gt; 0 
AND f. ` status `='working' 
The UNION 
SELECT 
A1. Mileage, 
A1. Departurecode, 
A1. Destinationcode, 
A1. Departurelescode, 
A1. Departurelesname, 
A1. Destinationlescode, 
A1. Destination_name, 
A1. Center_code, 
A1. Rrs_create_date, 
A1. Rrs_create_by, 
A1. Id, 
"', 
'
The FROM 
Md_lczsjm a1 
LEFT the JOIN (
SELECT DISTINCT 
Std_id 
The FROM 
Md_lczsjm_record mt 
LEFT the JOIN maintain_detail md ON md. Pre_merge_id=mt. Id 
LEFT the JOIN maintain m ON m.i d=md parent_id 
LEFT the JOIN flows ON flow_id f=f.i d 
WHERE 
Order_no & gt; 0 
IS NULL AND f.i d 
The UNION 
SELECT DISTINCT 
Std_id 
The FROM 
Md_lczsjm_record mt 
LEFT the JOIN maintain_detail md ON md. Pre_merge_id=mt. Id 
LEFT the JOIN maintain m ON m.i d=md parent_id 
LEFT the JOIN flows ON flow_id f=f.i d 
WHERE 
Order_no & gt; 0 
AND f. ` status `='working' 
) ON a a.s td_id=a1. Id 
WHERE 
A.s td_id IS NULL 
) a 
WHERE 
1=1 
AND 1=1 
LIMIT 0, 
10

Execution plan is as follows:

Description: the main problem is too much md_lczsjm_record data about 400 w query statement for the three statements came out again after the union limit every single check limit, soon to consider is the union is a full table found out after the limit, now time is 40 s hope to improve the query speed

CodePudding user response:

Can say your business logic, please, see the SQL feel logical weird, md_lczsjm_record table should not take so many times, you can use the or, exists as a substitute

CodePudding user response:

Record is records recorded the operation of the main table records, then this is the main table similar to the back into a state of data and the union of three part

CodePudding user response:

Paging, you try the view, or create a temporary table

CodePudding user response:

A doubt whether really need such a long SQL

In fact most of the time, was no good design, database design personnel to developers to write SQL optimization,

Some database design personnel blindly in pursuit of paradigm, as a result, developers don't know how to write SQL,

Personal think database design is as simple as possible, performance optimization is good for you to do so, table structure simple, query is simple, not performance, add a basic index are solved,