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