MySQL query running terribly slow
I have a rather large query that runs at an awful slow pace. At the start it took around 15 mins. Then I added all the appropriate indexes and got it down to 30 secs. This was yesterday. Now today, I added 2 new joins, and actually changed a few LEFT JOIN
's to INNER
, and it's now taking 20+ mins to run.
Here is the EXPLAIN
of the query. I uploaded an Excel file to SkyDrive with the results in it: http://cid-a32b31fdac0efced.office.live.com/view.aspx/mysqlexplain.xlsx
You will see here that there is a table, mwd, which it says has no key. I have checked this table and it does indeed have the appropriate index attached. However, this was not the table I added to the join and was running fine yesterday.
The new joins I created can be seen on lines 28 and 29.
Any help would be greatly appreciated.
EDIT: EDIT: I ran the query again without the fields from the mwd
table, ie. BIR3M
, dealer_code
, country_code
, dsm_dealer_number
, location_number
, dms_type
, sequence_number
, version_no_edr
, id_dms_type
, version_no_dms
, version_date_dms
, and it now runs in 4 secs! (It is still joining on this table)
I know that it can't find a key for mwd
, even though there is an index on the correct fields, so what can I do about that?
EDIT: Didn't really want to post the query, work stuff. But this is the first part of the join, which is causing the delay. It's pretty long...
SELECT mwd.dealer_code AS "BIR3M",
vm.chassis_number AS "VIN",
vm.Vehicle_ID,
vm.Registration,
COALESCE(vm.After_sale_type, "") AS "After Sales Type",
COALESCE(vm.Fabrication_No, "") AS "Fabrication No",
I.created_date AS "InvoiceDate",
I.department_id,
COALESCE(C.claim_number, "") AS "Intervention ID",
wb.booking_id AS "Booking ID",
wb.booking_date_time AS "Booking Date",
iba.account AS "Account Number",
acc.account_name AS "Account Description",
ibi.warranty_percentage AS "Payee Responsibility %",
IF(I.iscredit = 1, I.invoice_number, COALESCE(I2.invoice_number, "")) AS "Invoice Number",
IF(I.iscredit = 1, COALESCE(I2.invoice_number, "") , "") AS "Original Invoice Number",
"" AS "Part Reference",
"" AS "Part Description",
ibi.Booking_Time AS "Quantity",
0 AS "Cause Indicator",
"" AS "Stock Status",
ROUND(
(
CASE WHEN iBI.Claimed_Flag = 1 THEN (ibi.booking_time * ibi.warranty_labour_rate)
#(iBI.Claimed_Value * iBI.warranty_Labour_Rate)
WHEN iBi.Claimed_Flag = 0 THEN ibi.booking_time
#(iBI.Claimed_Value)
END
) # End
* CASE WHEN I.iscredit = 1 THEN -1 ELSE 1 END
,2) AS "RetailPrice",
ibia.discount AS "Discount Rate",
0.00 AS "Surcharge Rate",
ROUND(
ibi.Booking_Time * ibi.warranty_labour_rate * (1 - ibia.discount / 100) *
COALESCE(mwc.commercial_cont, 100) / 100 , 2)
AS "Cost Before Tax",
iba.account AS "Account",
CASE
WHEN ibi.warranty_percentage > 0 THEN "Warranty"
WHEN act.generic_type = 0 THEN "External"
WHEN act.generic_type = 1 THEN "Vehicle"
WHEN act.generic_type = 2 THEN "Internal"
WHEN act.generic_type = 3 THEN "Non Productive"
END AS "AccountType",
ibi.booking_time AS "Labour Time",
ibi.warranty_labour_rate AS "Hourly Rate",
0.00 AS "VAT Rate",
0.00 AS "VAT Total",
IBI.id AS "Invoice Booking Item ID",
IBI.operation_code AS "OPECOD",
IBI.job_description AS "Description",
CASE WHEN i.iscredit = 1 THEN 9 ELSE 0 END AS "Accounts Allocation",
COALESCE(mwc.contract_card_no, "") AS "Contract Card No",
COALESCE(mwc.vehicle_mileage, "") AS "Mileage",
COALESCE(mwc.delivery_date, "") AS "Delivery Date",
COALESCE(mwc.vo_number, "") AS "VO Number",
COALESCE(mwc.expense_code, "") AS "Expense Code",
COALESCE(mwc.catalog_function, "") AS "Catalog Function",
COALESCE(mwc.customer_complaint, "") AS "Customer Complaint",
COALESCE(mwc.commercial_cont, 100) AS "Commercial Contribution",
COALESCE(mwc.ots_otc_no, "") AS "OTS/OTC No",
COALESCE(mwc.supplier_code, "") AS "Supplier Code",
COALESCE(mwc.paint_code, "") AS "Paint Code",
COALESCE(mwc.off_road_breakdown, "") AS "Off Road Breakdown",
COALESCE(mwc.approval_no, "") AS "Approval No",
COALESCE(mwc.assistance_number, "") AS "Assistance Number",
COALESCE(mwc.customer_complaint_comment, "") AS "Customer Complaint Comment",
wb.booking_id AS "Repair Order Number",
wb.booking_date_time AS "Repair Order Date",
mwd.dealer_code,
mwd.country_code,
mwd.dsm_dealer_number,
mwd.location_number,
mwd.dms_type,
mwd.sequence_number,
mwd.version_no_edr,
mwd.id_dms_type,
mwd.version_no_dms,
mwd.version_date_dms,
COALESCE(mwc.customer_satisfied, "") AS "Customer Satisfied",
COALESCE(mwc.dealer_satisfied, "") AS "Dealer Satisfied",
COALESCE(mwc.parts_invoice_no, "") AS "Parts Invoice Number",
"" AS "Parts Type",
"" AS "Packaging Code",
CASE WHEN cr.Contact_ID IS NOT NULL THEN "P"
WHEN br.Business_ID IS NOT NULL THEN "S"
WHEN d.CODE IS NOT NULL THEN "S"
END AS "Customer Type",
"P" AS "Customer Vehicle Relation",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Surname
WHEN br.Business_ID IS NOT NULL THEN br.Company_Name
WHEN d.CODE IS NOT NULL THEN d.Description
END AS "Surname/Corporate Name",
"GB" AS "Language",
COALESCE(cr.Surname, "") AS "Second Name",
COALESCE(cr.Forename, "") AS "First Name",
COALESCE(cr.Date_Of_Birth, "") AS "DOB",
COALESCE(cr.Title, "") AS "Title",
COALESCE(vm.Registration_Date, "") AS "Registration Date",
CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Letter_Contact_Allowed = 1, "", "A")
WHEN br.Business_ID IS NOT NULL THEN IF(br.Letter_Contact_Allowed = 1, "", "A")
WHEN d.CODE IS NOT NULL THEN ""
END AS "Mail Contact",
CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.EMail_Contact_Allowed = 1, "", "E")
WHEN br.Business_ID IS NOT NULL THEN IF(br.EMail_Contact_Allowed = 1, "", "E")
WHEN d.CODE IS NOT NULL THEN ""
END AS "EMail Contact",
CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Fax_Contact_Allowed = 1, "", "F")
WHEN br.Business_ID IS NOT NULL THEN IF(br.Fax_Contact_Allowed = 1, "", "F")
WHEN d.CODE IS NOT NULL THEN ""
END AS "Fax Contact",
CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Telephone_Contact_Allowed = 1, "", "T")
WHEN br.Business_ID IS NOT NULL THEN IF(br.Telephone_Contact_Allowed = 1, "", "T")
WHEN d.CODE IS NOT NULL THEN ""
END AS "Telephone Contact",
COALESCE(CASE WHEN cr.Contact_ID IS NOT NULL THEN
CASE WHEN cr.Preferred_Contact_Method = 1 THEN "A"
WHEN cr.Preferred_Contact_Method = 2 THEN "E"
WHEN cr.Preferred_Contact_Method = 9 THEN "F"
WHEN cr.Preferred_Contact_Method = 5 THEN "T"
END
WHEN br.Business_ID IS NOT NULL THEN
CASE WHEN br.Preferred_Contact_Method = 1 THEN "A"
WHEN br.Preferred_Contact_Method = 2 THEN "E"
WHEN br.Preferred_Contact_Method = 9 THEN "F"
WHEN br.Preferred_Contact_Method = 5 THEN "T"
END
WHEN d.CODE IS NOT NULL THEN "E"
END, "") AS "Preferred Support Type",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_1
WHEN br.Business_ID IS NOT NULL THEN br.Address_1
WHEN d.CODE IS NOT NULL THEN ""
END AS "Street Name",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Postcode
WHEN br.Business_ID IS NOT NULL THEN br.Postcode
WHEN d.CODE IS NOT NULL THEN ""
END AS "Post Code",
"GBR" AS Country,
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_2
WHEN br.Business_ID IS NOT NULL THEN br.Address_2
WHEN d.CODE IS NOT NULL THEN ""
END AS "Address 1",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_3
WHEN br.Business_ID IS NOT NULL THEN br.Address_3
WHEN d.CODE IS NOT NULL THEN ""
END AS "Address 2",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_4
WHEN br.Business_ID IS NOT NULL THEN br.Address_4
WHEN d.CODE IS NOT NULL THEN ""
END AS "Address 3",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Email_Address
WHEN br.Business_ID IS NOT NULL THEN br.Email_Address
WHEN d.CODE IS NOT NULL THEN ""
END AS "Email",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Tel_num_home
WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_primary
WHEN d.CODE IS NOT NULL THEN ""
END AS "Phone Number 1",
CASE WHEN cr.Contact_ID IS NOT NULL THEN 开发者_Python百科cr.Tel_num_mobile
WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_secondary
WHEN d.CODE IS NOT NULL THEN ""
END AS "Phone Number 2",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Tel_num_business
WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_mobile
WHEN d.CODE IS NOT NULL THEN ""
END AS "Phone Number 3",
CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Fax_num
WHEN br.Business_ID IS NOT NULL THEN br.Fax_num
WHEN d.CODE IS NOT NULL THEN ""
END AS "Fax Number",
wb.Owner_ID AS "Owner",
1 AS "Labour Flag",
"WARRANTY"
FROM
I
INNER JOIN iba ON I.monetary_transaction_id = iba.monetary_transaction_id
INNER JOIN ibi ON ibi.booking_id = iba.booking_id AND ibi.job_group = iba.job_group AND ibi.sequence = iba.sequence
INNER JOIN G ON G.Booking_item_id= Ibi.ID
AND g.Type_ID = 1
INNER JOIN C ON C.ID = G.Claim_Booking_claim_ID
INNER JOIN WM ON WM.Warranty_Booking_Claim_ID = C.ID
INNER JOIN mwc ON mwc.ID = WM.Manufacturer_Warranty_Claim_ID
INNER JOIN ibia ON ibia.booking_item_id = ibi.id
AND ibia.sequence = ibi.sequence AND ibia.booking_account_id = iba.id AND ibia.sequence = iba.sequence
INNER JOIN wb ON wb.booking_id = ibi.booking_id
INNER JOIN a ON a.account_id = iba.account
INNER JOIN act ON act.type = a.type
LEFT JOIN ibev ON ibev.booking_item_id = ibi.id AND ibi.sequence = ibev.sequence
INNER JOIN vm ON wb.vehicle_id = vm.vehicle_id
INNER JOIN mwd ON mwd.department_id = I.department_id
INNER JOIN mt ON I.monetary_transaction_id = mt.master_monetary_transaction_id
INNER JOIN I2 ON mt.id = I2.monetary_transaction_id
INNER JOIN acc ON iba.account = acc.account_id
LEFT JOIN cr ON wb.owner_id = cr.contact_id
LEFT JOIN br ON wb.owner_id = br.business_id
LEFT JOIN d ON wb.owner_id = d.code
WHERE I.department_id IN (120, 322, 362)
AND I.created_date BETWEEN '2011-03-01 00:00:00' AND '2011-03-02 23:59:59'
AND ibev.booking_item_id IS NULL AND NOT ibi.booking_item_type_id IN (10,14) AND ibi.warranty_percentage > 0
AND wb.booking_id IN (454017, 454019, 454021, 454031)
Whenever your query involves so many tables - it usually smacks of a design issue. Leaving that aside, I notice that you have a mix of LEFT (OUTER) and INNER JOINs.
Unless there is a specific dependency between the tables, e.g.
A LEFT JOIN B on (a..b) INNER JOIN C on (c..b)
(C must come after b)
You should group together all the INNER JOINs as early as possible, e.g. instead of
A LEFT JOIN B on (a..b) INNER JOIN C on (c..a)
write it as
A INNER JOIN C on (c..a) LEFT JOIN B on (a..b)
That will help the optimizer process the INNER JOINs first, which potentially reduces the row count. LEFT JOINs by definition retain all the rows from the preceeding tables, so the optimizer usually does those succeeding tables later.
OK, I rejigged the order of the JOIN
's and moved:
INNER JOIN mwd ON mwd.department_id = I.department_id
To be the first JOIN
. Now the query takes 8 secs! So that's that fixed. But can anyone explain why this would occur?
Until now I can see optimization in the end of the query:
For example this can be rewritten
WHERE I.department_id IN (120, 322, 362)
AND I.created_date BETWEEN '2011-03-01 00:00:00' AND '2011-03-02 23:59:59'
AND ibev.booking_item_id IS NULL AND NOT ibi.booking_item_type_id IN (10,14) AND ibi.warranty_percentage > 0
AND wb.booking_id IN (454017, 454019, 454021, 454031)
into
JOIN (SELECT 120 AS d
UNION ALL
SELECT 322 AS d
UNION ALL
SELECT 362) d1
ON d1.d = i.department_id
JOIN (SELECT 454017 AS b
UNION ALL
SELECT 454019 AS b
UNION ALL
SELECT 454021 AS b
UNION ALL
SELECT 454031 AS b) d2
ON d2.b = wb.booking_id
LEFT JOIN (SELECT 10 AS bt
UNION ALL
SELECT 14 AS bt) d3
ON d3.bt <> ibi.booking_item_type_id
WHERE i.created_date BETWEEN '2011-03-01 00:00:00' AND '2011-03-02 23:59:59'
AND ibev.booking_item_id IS NULL
AND ibi.warranty_percentage > 0
where we taken the IN options and put in derived static tables, so that on their match - the ON clause - the indexes should be used. MySQL uses indexes on the joins but not in the IN clause.
You need too add indexes on the following columns
- I.department_id
- wb.booking_id
- ibi.booking_item_type_id
精彩评论