0
votes

I got this query running on MySQL 5.7 and MariaDB-10.1.8 .


    SELECT  
      src.LINE AS ExcelRowNumber,
      bveh.BaseVehicleID,
      pa.Part_Id,
      CASE
        WHEN model.ModelID IS NOT NULL THEN model.ModelID
        ELSE 0
      END AS ModelID,
      CASE
        WHEN ebas.EngineBaseID IS NOT NULL THEN ebas.EngineBaseID
        ELSE 0
      END AS EngineBaseID,
      CASE
        WHEN smod.SubModelId IS NOT NULL THEN smod.SubModelId
        ELSE 0
      END AS SubModelId,
      CASE
        WHEN mbco.MfrBodyCodeID IS NOT NULL THEN mbco.MfrBodyCodeID
        ELSE 0
      END AS MfrBodyCodeID,
      CASE
        WHEN bndo.BodyNumDoorsID IS NOT NULL THEN bndo.BodyNumDoorsID
        ELSE 0
      END AS BodyNumDoorsID,
      CASE
        WHEN btyp.BodyTypeID IS NOT NULL THEN btyp.BodyTypeID
        ELSE 0
      END AS BodyTypeID,
      CASE
        WHEN dtyp.DriveTypeID IS NOT NULL THEN dtyp.DriveTypeID
        ELSE 0
      END AS DriveTypeID,
      CASE
        WHEN edes.EngineDesignationID IS NOT NULL THEN edes.EngineDesignationID
        ELSE 0
      END AS EngineDesignationID,
      CASE
        WHEN evin.EngineVINID IS NOT NULL THEN evin.EngineVINID
        ELSE 0
      END AS EngVINID,
      CASE
        WHEN fdco.FuelDeliveryConfigID IS NOT NULL THEN fdco.FuelDeliveryConfigID
        ELSE 0
      END AS FuelDeliveryConfigID,
      CASE
        WHEN fdty.FuelDeliveryTypeID IS NOT NULL THEN fdty.FuelDeliveryTypeID
        ELSE 0
      END AS FuelDeliveryTypeID,
      CASE
        WHEN fdst.FuelDeliverySubTypeID IS NOT NULL THEN fdst.FuelDeliverySubTypeID
        ELSE 0
      END AS FuelDeliverySubTypeID,
      CASE
        WHEN fsct.FuelSystemControlTypeID IS NOT NULL THEN fsct.FuelSystemControlTypeID
        ELSE 0
      END AS FuelSysControlTypeID,
      CASE
        WHEN fsde.FuelSystemDesignID IS NOT NULL THEN fsde.FuelSystemDesignID
        ELSE 0
      END AS FuelSystemDesignID,
      CASE
        WHEN aspi.AspirationID IS NOT NULL THEN aspi.AspirationID
        ELSE 0
      END AS AspirationID,
      CASE
        WHEN chty.CylinderHeadTypeID IS NOT NULL THEN chty.CylinderHeadTypeID
        ELSE 0
      END AS CylHeadTypeID,
      CASE
        WHEN ftyp.FuelTypeID IS NOT NULL THEN ftyp.FuelTypeID
        ELSE 0
      END AS FuelTypeID,
      CASE
        WHEN isty.IgnitionSystemTypeID IS NOT NULL THEN isty.IgnitionSystemTypeID
        ELSE 0
      END AS IgnitionSystemTypeID,
      CASE
        WHEN ttyp.TransmissionTypeID IS NOT NULL THEN ttyp.TransmissionTypeID
        ELSE 0
      END AS TransmissionTypeID,
      CASE
        WHEN tbas.TransmissionBaseID IS NOT NULL THEN tbas.TransmissionBaseID
        ELSE 0
      END AS TransmissionBaseID,
      CASE
        WHEN tcty.TransmissionControlTypeID IS NOT NULL THEN tcty.TransmissionControlTypeID
        ELSE 0
      END AS TransmissionControlTypeID,
      CASE
        WHEN tmco.TransmissionMfrCodeID IS NOT NULL THEN tmco.TransmissionMfrCodeID
        ELSE 0
      END AS TransmissionMfrCodeID,
      CASE
        WHEN tnsp.TransmissionNumSpeedsID IS NOT NULL THEN tnsp.TransmissionNumSpeedsID
        ELSE 0
      END AS TransmissionNumSpeedsID,
      CASE
        WHEN elec.ElecControlledID IS NOT NULL THEN elec.ElecControlledID
        ELSE 0
      END AS TransmissionElecControlledID,
      CASE
        WHEN bedtyp.BedTypeID IS NOT NULL THEN bedtyp.BedTypeID
        ELSE 0
      END AS BedTypeID,
      CASE
        WHEN blen.BedLengthID IS NOT NULL THEN blen.BedLengthID
        ELSE 0
      END AS BedLengthID,
      CASE
        WHEN bcon.BedConfigID IS NOT NULL THEN bcon.BedConfigID
        ELSE 0
      END AS BedConfigID,
      CASE
        WHEN wbas.WheelBaseID IS NOT NULL THEN wbas.WheelBaseID
        ELSE 0
      END AS WheelBaseID,
      CASE
        WHEN brtyf.BrakeTypeID IS NOT NULL THEN brtyf.BrakeTypeID
        ELSE 0
      END AS FrontBrakeTypeID,
      CASE
        WHEN brtyr.BrakeTypeID IS NOT NULL THEN brtyr.BrakeTypeID
        ELSE 0
      END AS RearBrakeTypeID,
      CASE
        WHEN stypf.SpringTypeID IS NOT NULL THEN stypf.SpringTypeID
        ELSE 0
      END AS FrontSpringTypeID,
      CASE
        WHEN stypr.SpringTypeID IS NOT NULL THEN stypr.SpringTypeID
        ELSE 0
      END AS RearSpringTypeID,
      CASE
        WHEN bsys.BrakeSystemID IS NOT NULL THEN bsys.BrakeSystemID
        ELSE 0
      END AS BrakeSystemID,
      CASE
        WHEN babs.BrakeABSID IS NOT NULL THEN babs.BrakeABSID
        ELSE 0
      END AS BrakeABSID,
      CASE
        WHEN steertyp.SteeringTypeID IS NOT NULL THEN steertyp.SteeringTypeID
        ELSE 0
      END AS SteeringTypeID,
      CASE
        WHEN steersys.SteeringSystemID IS NOT NULL THEN steersys.SteeringSystemID
        ELSE 0
      END AS SteeringSystemID,
      CASE
        WHEN regi.RegionID IS NOT NULL THEN regi.RegionID
        ELSE 0
      END AS RegionAbbrID,
      CASE
        WHEN ever.EngineVersionID IS NOT NULL THEN ever.EngineVersionID
        ELSE 0
      END AS EngineVersionID,
      CASE
        WHEN valv.ValvesID IS NOT NULL THEN valv.ValvesID
        ELSE 0
      END AS EngineValvesID,
      CASE
        WHEN pout.PowerOutputID IS NOT NULL THEN pout.PowerOutputID
        ELSE 0
      END AS PowerOutputID,
      CASE
        WHEN vtyp.VehicleTypeID IS NOT NULL THEN vtyp.VehicleTypeID
        ELSE 0
      END AS VehicleTypeID,
      CASE
        WHEN engMfr.MfrID IS NOT NULL THEN engMfr.MfrID
        ELSE 0
      END AS EngineMfrID,
      IsValidable_yn AS IsValidable,
      src.Year,
      src.Product,
      pl.Product_Line_Id,
      src.PartNumber,
      src.MfrLabel,
      src.Qty,
      src.Picture1,
      src.Picture2,
      src.DateAddedPart,
      src.DateModifiedPart,
      src.ApplicationNote1 AS Note1,
      src.ApplicationNote2 AS Note2,
      src.ApplicationNote3 AS Note3,
      src.ApplicationNote4 AS Note4,
      src.ApplicationNote5 AS Note5,
      src.ApplicationNote6 AS Note6,
      src.ApplicationNote7 AS Note7,
      src.ApplicationNote8 AS Note8,
      src.ApplicationNote9 AS Note9,
      src.ApplicationNote10 AS Note10,
      src.PublishingNote1,
      src.PublishingNote2,
      src.PublishingNote3,
      src.PublishingNote4,
      src.PublishingNote5,
      src.Foot_note_numbers,
      src.Foot_note_values,
      CASE
        WHEN transMfr.MfrID IS NOT NULL THEN transMfr.MfrID
        ELSE 0
      END AS TransmissionMfrID,
      src.DateAdded,
      src.DateModified,
      src.SpecialInfo1,
      src.SpecialInfo2,
      src.SpecialInfo3,
      src.LifeCycle_Pies_Code_read_only,
      src.LifeCycle_Desc_short,
      src.LifeCycle_Desc_Long_read_only,
      src.PopCode,
      src.PopCode_Desc_read_only,
      src.AssetLogicalName,
      src.AssetItemRef,
      src.AssetItemOrder,
      src.AssetFileName,
      src.Action
    FROM pcdmb_oevierol_oct26_pc75.ExcelToSQLTemporaryTable src

    JOIN pcdmb_oevierol_oct26_pc75.Brand brand
      ON brand.BrandId = CAST(1 AS UNSIGNED)
    JOIN pcdmb_oevierol_oct26_pc75.Product_Line pl
      ON src.Product = pl.Product_Line_En
      AND pl.BrandID = brand.BrandID
    LEFT JOIN pcdmb_oevierol_oct26_pc75.Part pa
      ON src.PartNumber = pa.PartNumber
      AND pa.Product_Line_Id = pl.Product_Line_Id
    LEFT JOIN vcdb_sept2016.Make mak
      ON mak.MakeName = src.Make
    LEFT JOIN vcdb_sept2016.VehicleType vtyp
      ON (vtyp.VehicleTypeName = src.VehicleType)
    LEFT JOIN vcdb_sept2016.Model model
      ON model.ModelName = src.Model
      AND (model.VehicleTypeID = vtyp.VehicleTypeID
      OR src.VehicleType = '')
    JOIN vcdb_sept2016.BaseVehicle bveh
      ON bveh.YearID = src.Year
      AND bveh.MakeID = mak.MakeID
      AND bveh.ModelID = model.ModelID


    LEFT JOIN vcdb_sept2016.EngineBase ebas
      ON (ebas.Liter = src.EngineLiters)
      AND (ebas.CC = src.EngineCC)
      AND (ebas.CID = src.EngineCID)
      AND (ebas.Cylinders = src.EngineCylinder)
      AND (ebas.BlockType = src.EngineBlock)
      AND (ebas.EngBoreIn = src.EngBoreInch)
      AND (ebas.EngBoreMetric = src.EngBoreMetric)
      AND (ebas.EngStrokeIn = src.EngStrokeInch)
      AND (ebas.EngStrokeMetric = src.EngStrokeMetric)

    LEFT JOIN vcdb_sept2016.SubModel smod
      ON (smod.SubmodelName = src.Submodel)
    LEFT JOIN vcdb_sept2016.MfrBodyCode mbco
      ON (mbco.MfrBodyCodeName = src.MfrBodyCode)
    LEFT JOIN vcdb_sept2016.BodyNumDoors bndo
      ON (bndo.BodyNumDoors = src.BodyNumDoors)
    LEFT JOIN vcdb_sept2016.BodyType btyp
      ON (btyp.BodyTypeName = src.BodyType)
    LEFT JOIN vcdb_sept2016.DriveType dtyp
      ON (dtyp.DriveTypeName = src.DriveType)
    LEFT JOIN vcdb_sept2016.EngineDesignation edes
      ON (edes.EngineDesignationName = src.EngineDesignation)
    LEFT JOIN vcdb_sept2016.EngineVIN evin
      ON (evin.EngineVINName = src.EngineVIN)
    LEFT JOIN vcdb_sept2016.FuelDeliveryType fdty
      ON (fdty.FuelDeliveryTypeName = src.FuelDeliveryType)
    LEFT JOIN vcdb_sept2016.FuelDeliverySubType fdst
      ON (fdst.FuelDeliverySubTypeName = src.FuelDeliverySubType)
    LEFT JOIN vcdb_sept2016.FuelSystemControlType fsct
      ON (fsct.FuelSystemControlTypeName = src.FuelSystemControlType)
    LEFT JOIN vcdb_sept2016.FuelSystemDesign fsde
      ON (fsde.FuelSystemDesignName = src.FuelSystemDesign)
    LEFT JOIN vcdb_sept2016.FuelDeliveryConfig fdco
      ON (fdco.FuelDeliveryTypeID = fdty.FuelDeliveryTypeID
      AND fdco.FuelDeliverySubTypeID = fdst.FuelDeliverySubTypeID
      AND fdco.FuelSystemControlTypeID = fsct.FuelSystemControlTypeID
      AND fdco.FuelSystemDesignID = fsde.FuelSystemDesignID)
    LEFT JOIN vcdb_sept2016.Aspiration aspi
      ON (aspi.AspirationName = src.Aspiration)
    LEFT JOIN vcdb_sept2016.CylinderHeadType chty
      ON (chty.CylinderHeadTypeName = src.CylinderHeadType)
    LEFT JOIN vcdb_sept2016.FuelType ftyp
      ON (ftyp.FuelTypeName = src.FuelType)
    LEFT JOIN vcdb_sept2016.IgnitionSystemType isty
      ON (isty.IgnitionSystemTypeName = src.IgnitionSystemType)
    LEFT JOIN vcdb_sept2016.Mfr transMfr
      ON (transMfr.MfrName = src.TransmissionMfr)
    LEFT JOIN vcdb_sept2016.TransmissionType ttyp
      ON (ttyp.TransmissionTypeName = src.TransmissionType)
    LEFT JOIN vcdb_sept2016.TransmissionControlType tcty
      ON (tcty.TransmissionControlTypeName = src.TransmissionControlType)
    LEFT JOIN vcdb_sept2016.TransmissionMfrCode tmco
      ON (tmco.TransmissionMfrCode = src.TransmissionMfrCode)
    LEFT JOIN vcdb_sept2016.TransmissionNumSpeeds tnsp
      ON (tnsp.TransmissionNumSpeeds = src.TransmissionNumSpeeds)
    LEFT JOIN vcdb_sept2016.ElecControlled elec
      ON elec.ElecControlled = src.TransmissionElecControlled

    LEFT JOIN vcdb_sept2016.TransmissionBase tbas
      ON (tbas.TransmissionTypeID = ttyp.TransmissionTypeID
      AND tbas.TransmissionNumSpeedsID = tnsp.TransmissionNumSpeedsID
      AND tbas.TransmissionControlTypeID = tcty.TransmissionControlTypeID)
    LEFT JOIN vcdb_sept2016.BedLength blen
      ON (blen.BedLength = src.BedLength)
    LEFT JOIN vcdb_sept2016.BedType bedtyp
      ON (bedtyp.BedTypeName = src.BedType)

    LEFT JOIN vcdb_sept2016.BedConfig bcon
      ON (bcon.BedLengthID = blen.BedLengthID
      AND bcon.BedTypeID = bedtyp.BedTypeID)
    LEFT JOIN vcdb_sept2016.WheelBase wbas
      ON (wbas.WheelBase = src.WheelBase)
    LEFT JOIN vcdb_sept2016.BrakeType brtyf
      ON (brtyf.BrakeTypeName = src.FrontBrakeType)
    LEFT JOIN vcdb_sept2016.BrakeType brtyr
      ON (brtyr.BrakeTypeName = src.RearBrakeType)
    LEFT JOIN vcdb_sept2016.SpringType stypf
      ON (stypf.SpringTypeName = src.FrontSpringType)
    LEFT JOIN vcdb_sept2016.SpringType stypr
      ON (stypr.SpringTypeName = src.RearSpringType)
    LEFT JOIN vcdb_sept2016.BrakeSystem bsys
      ON (bsys.BrakeSystemName = src.BrakeSystem)
    LEFT JOIN vcdb_sept2016.BrakeABS babs
      ON (babs.BrakeABSName = src.BrakeABS)
    LEFT JOIN vcdb_sept2016.SteeringType steertyp
      ON (steertyp.SteeringTypeName = src.SteeringType)
    LEFT JOIN vcdb_sept2016.SteeringSystem steersys
      ON (steersys.SteeringSystemName = src.SteeringSystem)
    LEFT JOIN vcdb_sept2016.Region regi
      ON (regi.RegionAbbr = src.Region)
    LEFT JOIN vcdb_sept2016.EngineVersion ever
      ON (ever.EngineVersion = src.EngineVersion)
    LEFT JOIN vcdb_sept2016.Valves valv
      ON (valv.ValvesPerEngine = src.EngineValves)
    LEFT JOIN vcdb_sept2016.PowerOutput pout
      ON (pout.HorsePower = src.PowerOutput)
    LEFT JOIN vcdb_sept2016.Mfr engMfr
      ON engMfr.MfrName = src.EngineMfr
    WHERE NOT EXISTS (SELECT
      LINE
    FROM pcdmb_oevierol_oct26_pc75.ValidationErrTempTable temp
    WHERE temp.LINE = src.LINE
    AND isUK != 1
    AND ErrType NOT LIKE 'Warning%')

Also here's the explain :

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY brand       const   PRIMARY PRIMARY 4   const   1   100 Using index
1   PRIMARY src     ALL                 299 100 Using where
1   PRIMARY vtyp        index       vtyp_x1 52      11  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY pl      ALL BrandId             359 9.94    Using where; Using join buffer (Block Nested Loop)
1   PRIMARY ebas        index       ebas_x1 65      3710    100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY smod        index       smod_x1 52      3574    100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY mbco        index       mbco_ix1    12      3865    100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY bndo        index       bndo_ix1    3       8   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY btyp        index       btyp_ix1    52      87  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY dtyp        index       dtyp_ix1    32      22  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY pa      ref PART_UK_1,PRODUCT_LINE_ID   PART_UK_1   773 pcdmb_oevierol_oct26_pc75.src.PartNumber,pcdmb_oevierol_oct26_pc75.pl.Product_Line_id   1   100 Using where; Using index
1   PRIMARY bveh        ref IDX_BaseVehicle_MakeID,IDX_BaseVehicle_ModelID,IDX_BaseVehicle_YearID   IDX_BaseVehicle_YearID  4   pcdmb_oevierol_oct26_pc75.src.Year  569 100 Using index condition
1   PRIMARY mak     eq_ref  PRIMARY PRIMARY 4   vcdb_sept2016.bveh.MakeID   1   100 Using where
1   PRIMARY model       eq_ref  PRIMARY,IDX_Model_VehicleTypeID PRIMARY 4   vcdb_sept2016.bveh.ModelID  1   100 Using where
1   PRIMARY edes        index       edes_ix1    32      2645    100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY evin        index       evin_ix1    7       91  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY fdty        index       fdty_ix1    52      3   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY fdst        index       fdst_ix1    52      33  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY fsct        index       fsct_ix1    52      5   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY fsde        index       fsde_ix1    52      140 100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY fdco        eq_ref  fdco_ix1,IDX_FuelDeliveryConfig_FuelDel1,IDX_FuelDeliveryConfig_FuelDel2,IDX_FuelDeliveryConfig_FuelSys3,IDX_FuelDeliveryConfig_FuelSys4    fdco_ix1    16  vcdb_sept2016.fdty.FuelDeliveryTypeID,vcdb_sept2016.fdst.FuelDeliverySubTypeID,vcdb_sept2016.fsct.FuelSystemControlTypeID,vcdb_sept2016.fsde.FuelSystemDesignID 1   100 Using index
1   PRIMARY aspi        index       aspi_ix1    32      9   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY chty        index       chty_ix1    32      8   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY ftyp        index       ftyp_ix1    32      13  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY isty        index       isty_ix1    32      6   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY transMfr        index       mfr_ix1 32      148 100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY ttyp        index       ttyp_ix1    32      5   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY tcty        index       tcty_ix1    32      6   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY tmco        index       tmco_ix1    32      2250    100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY tnsp        index       tnsp_ix1    3       13  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY elec        index       econ_ix1    3       5   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY tbas        eq_ref  transb_ix1,IDX_TransmissionBase_Transmiss1,IDX_TransmissionBase_Transmiss2,IDX_TransmissionBase_Transmiss3  transb_ix1  12  vcdb_sept2016.ttyp.TransmissionTypeID,vcdb_sept2016.tnsp.TransmissionNumSpeedsID,vcdb_sept2016.tcty.TransmissionControlTypeID   1   100 Using index
1   PRIMARY blen        index       blen_ix1    10      132 100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY bedtyp      index       btyp_ix1    52      8   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY bcon        ref IDX_BedConfig_BedLengthID,IDX_BedConfig_BedTypeID   IDX_BedConfig_BedLengthID   4   vcdb_sept2016.blen.BedLengthID  1   100 Using where
1   PRIMARY wbas        index       wbas_ix1    12      394 100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY brtyf       index       btyp_ix1    32      3   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY brtyr       index       btyp_ix1    32      3   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY stypf       index       styp_ix1    52      9   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY stypr       index       styp_ix1    52      9   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY bsys        index       bsys_ix1    32      8   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY babs        index       babs_ix1    32      5   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY steertyp        index       stee_ix1    32      4   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY steersys        index       ssys_ix1    32      4   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY regi        index       regi_ix1    3       3   100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY ever        index       ever_ix1    22      117 100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY valv        index       valv_ix1    3       24  100 Using where; Using index; Using join buffer (Block Nested Loop)
1   PRIMARY pout        ALL                 1074    100 Using where; Using join buffer (Block Nested Loop)
1   PRIMARY engMfr      index       mfr_ix1 32      148 100 Using where; Using index; Using join buffer (Block Nested Loop)
2   DEPENDENT SUBQUERY  temp        ALL                 10  10  Using where

My ExcelToSQLTemporaryTable (Alias:src) have about 10 000 rows

In the first case (MySQL 5.7), this query is not complete after 2 hours, and in the second case (MariaDb), this query take about 3 minutes to complete. The problem is that my boss want to stick to MySQL 5.7 so I need some help to have the same performances as MariaDB in MySQL 5.7.

3
This is a configuration issue. You need to be aware that MySQL ships with a very conservative configuration so it can work on old, slow computers. Find your my.cnf and look for the value of innodb_buffer_pool_size variable and increase it. To see what that variable does, stick it in google and you'll get everything you need to know. Even though the query is stupidly big, it's quite simple since it's just many joins. With larger buffer pool, it should be fast. Speed is also dependent on how much data is being returned, because it takes a while to transfer it over network.Mjh

3 Answers

1
votes

I had similar issue. My query with multiple left joins was running very slow. I tried many types of collation and using latin1_swedish_ci for all DB tables involved in query did the job. I also added one primary key column in select query to speed up execution. It worked for me.

0
votes

After trying to tune my configuration file for a long time while optimizing queries to only gain scratches, I found out that the databases that I joined did not have the same character set. When I fixed this, everything work like a charm.

-1
votes
  1. You did not write how much data in mysql table ( number of records ).
  2. as you said you did index , so I am assuming you did proper indexing
  3. MySql is using different type of database engines for different purpose, from your query I can see that you need InoDb database engine which supports foreign keys constrain ,
  4. map your tables with primay and foreign keys (which you used for making join) and let me know does it improve in speed ?