[Solved] SQL Query Stuck in Statistics State

EverSQL Database Performance Knowledge Base

SQL Query Stuck in Statistics State

I've been troubleshooting this complex query today, thinking it was a performance issue. It appears that the query is getting stuck into the 'Statistics' state. It is currently in that state for 1300 seconds.

I've checked the indexes for the tables involved -- I've optimized the table -- what could be causing this hang?

SELECT
    Import_Values.id,
    Import_Values.part_id,
    Import_Values.qty,
    Import_Values.note,
    Parts.partterminologyname,
    GROUP_CONCAT(BaseVehicle.YearID, ' ', Make.MakeName, ' ', Model.modelname, ' ', SubModel.SubModelName SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineDesignation.EngineDesignationName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineVIN.EngineVINName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.Liter) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.CC) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.CID) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.Cylinders) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.BlockType) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.EngBoreIn) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.EngBoreMetric) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.EngStrokeIn) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineBase.EngStrokeMetric) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(FuelDeliveryType.FuelDeliveryTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(FuelDeliverySubType.FuelDeliverySubTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(FuelSystemControlType.FuelSystemControlTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(FuelSystemDesign.FuelSystemDesignName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(Aspiration.AspirationName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(CylinderHeadType.CylinderHeadTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(FuelType.FuelTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(IgnitionSystemType.IgnitionSystemTypeName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(Mfr.MfrName) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(EngineVersion.EngineVersion) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(Valves.ValvesPerEngine) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(BedLength.BedLength) SEPARATOR ', '),
    GROUP_CONCAT(DISTINCT(BedLength.BedLengthMetric) SEPARATOR ', ')
    FROM 
    Import_Values
    INNER JOIN BaseVehicle 
        ON Import_Values.base_vehicle_id=BaseVehicle.BaseVehicleID
    INNER JOIN Parts 
        ON Import_Values.part_type_id=Parts.PartTerminologyID
    INNER JOIN Make
        ON BaseVehicle.MakeID=Make.MakeID
    INNER JOIN Model
        ON BaseVehicle.ModelID=Model.ModelID
    INNER JOIN Vehicle
        ON Import_Values.base_vehicle_id=Vehicle.BaseVehicleID
    INNER JOIN SubModel
        ON Vehicle.SubModelID=SubModel.SubModelID
    INNER JOIN VehicleToEngineConfig
        ON Vehicle.VehicleID=VehicleToEngineConfig.VehicleID
    INNER JOIN EngineConfig
        ON VehicleToEngineConfig.EngineConfigID=EngineConfig.EngineConfigID
    INNER JOIN EngineDesignation
        ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID
    INNER JOIN EngineVIN
        ON EngineConfig.EngineVINID=EngineVIN.EngineVINID
    INNER JOIN EngineBase
        ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
    INNER JOIN FuelDeliveryConfig
        ON EngineConfig.FuelDeliveryConfigID=FuelDeliveryConfig.FuelDeliveryConfigID
    INNER JOIN FuelDeliveryType
        ON FuelDeliveryConfig.FuelDeliveryTypeID=FuelDeliveryType.FuelDeliveryTypeID
    INNER JOIN FuelDeliverySubType
        ON FuelDeliveryConfig.FuelDeliverySubTypeID=FuelDeliverySubType.FuelDeliverySubTypeID
    INNER JOIN FuelSystemControlType
        ON FuelDeliveryConfig.FuelSystemControlTypeID=FuelSystemControlType.FuelSystemControlTypeID
    INNER JOIN FuelSystemDesign
        ON FuelDeliveryConfig.FuelSystemDesignID=FuelSystemDesign.FuelSystemDesignID
    INNER JOIN Aspiration
        ON EngineConfig.AspirationID=Aspiration.AspirationID
    INNER JOIN CylinderHeadType
        ON EngineConfig.CylinderHeadTypeID=CylinderHeadType.CylinderHeadTypeID
    INNER JOIN FuelType
        ON EngineConfig.FuelTypeID=FuelType.FuelTypeID
    INNER JOIN IgnitionSystemType
        ON EngineConfig.IgnitionSystemTypeID=IgnitionSystemType.IgnitionSystemTypeID
    INNER JOIN Mfr
        ON EngineConfig.EngineMfrID=Mfr.MfrID
    INNER JOIN EngineVersion
        ON EngineConfig.EngineVersionID=EngineVersion.EngineVersionID
    INNER JOIN Valves
        ON EngineConfig.ValvesID=Valves.Valvesid
    INNER JOIN VehicleToBedConfig
        ON Vehicle.VehicleID=VehicleToBedConfig.VehicleID
    INNER JOIN BedConfig
        ON VehicleToBedConfig.BedConfigID=BedConfig.BedConfigID
    INNER JOIN BedLength
        ON BedConfig.BedLengthID=BedLength.BedLengthID
    GROUP BY part_id

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid using too many joins (modified query below): Using too many joins can complicate the query's readability and the ability of the database optimizer to optimize the query.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `Aspiration` ADD INDEX `aspiration_idx_aspirationid` (`AspirationID`);
ALTER TABLE `BaseVehicle` ADD INDEX `basevehicle_idx_basevehicleid` (`BaseVehicleID`);
ALTER TABLE `BedConfig` ADD INDEX `bedconfig_idx_bedconfigid` (`BedConfigID`);
ALTER TABLE `BedLength` ADD INDEX `bedlength_idx_bedlengthid` (`BedLengthID`);
ALTER TABLE `CylinderHeadType` ADD INDEX `cylinderheadtype_idx_cylinderheadtypeid` (`CylinderHeadTypeID`);
ALTER TABLE `EngineBase` ADD INDEX `enginebase_idx_enginebaseid` (`EngineBaseID`);
ALTER TABLE `EngineConfig` ADD INDEX `engineconfig_idx_engineconfigid` (`EngineConfigID`);
ALTER TABLE `EngineDesignation` ADD INDEX `enginedesignation_idx_enginedesignationid` (`EngineDesignationID`);
ALTER TABLE `EngineVIN` ADD INDEX `enginevin_idx_enginevinid` (`EngineVINID`);
ALTER TABLE `EngineVersion` ADD INDEX `engineversion_idx_engineversionid` (`EngineVersionID`);
ALTER TABLE `FuelDeliveryConfig` ADD INDEX `fueldeliveryconfig_idx_fueldeliveryconfigid` (`FuelDeliveryConfigID`);
ALTER TABLE `FuelDeliverySubType` ADD INDEX `fueldeliverysubtyp_idx_fueldeliverysubtypeid` (`FuelDeliverySubTypeID`);
ALTER TABLE `FuelDeliveryType` ADD INDEX `fueldeliverytype_idx_fueldeliverytypeid` (`FuelDeliveryTypeID`);
ALTER TABLE `FuelSystemControlType` ADD INDEX `fuelsystemcontrolt_idx_fuelsystemcontroltypeid` (`FuelSystemControlTypeID`);
ALTER TABLE `FuelSystemDesign` ADD INDEX `fuelsystemdesign_idx_fuelsystemdesignid` (`FuelSystemDesignID`);
ALTER TABLE `FuelType` ADD INDEX `fueltype_idx_fueltypeid` (`FuelTypeID`);
ALTER TABLE `IgnitionSystemType` ADD INDEX `ignitionsystemtype_idx_ignitionsystemtypeid` (`IgnitionSystemTypeID`);
ALTER TABLE `Import_Values` ADD INDEX `import_values_idx_part_id` (`part_id`);
ALTER TABLE `Make` ADD INDEX `make_idx_makeid` (`MakeID`);
ALTER TABLE `Mfr` ADD INDEX `mfr_idx_mfrid` (`MfrID`);
ALTER TABLE `Model` ADD INDEX `model_idx_modelid` (`ModelID`);
ALTER TABLE `Parts` ADD INDEX `parts_idx_partterminologyid` (`PartTerminologyID`);
ALTER TABLE `SubModel` ADD INDEX `submodel_idx_submodelid` (`SubModelID`);
ALTER TABLE `Valves` ADD INDEX `valves_idx_valvesid` (`Valvesid`);
ALTER TABLE `Vehicle` ADD INDEX `vehicle_idx_basevehicleid` (`BaseVehicleID`);
ALTER TABLE `VehicleToBedConfig` ADD INDEX `vehicletobedconfig_idx_vehicleid` (`VehicleID`);
ALTER TABLE `VehicleToEngineConfig` ADD INDEX `vehicletoenginecon_idx_vehicleid` (`VehicleID`);
The optimized query:
SELECT
        Import_Values.id,
        Import_Values.part_id,
        Import_Values.qty,
        Import_Values.note,
        Parts.partterminologyname,
        GROUP_CONCAT(BaseVehicle.YearID,
        ' ',
        Make.MakeName,
        ' ',
        Model.modelname,
        ' ',
        SubModel.SubModelName SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineDesignation.EngineDesignationName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineVIN.EngineVINName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.Liter) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.CC) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.CID) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.Cylinders) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.BlockType) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.EngBoreIn) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.EngBoreMetric) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.EngStrokeIn) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineBase.EngStrokeMetric) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (FuelDeliveryType.FuelDeliveryTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (FuelDeliverySubType.FuelDeliverySubTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (FuelSystemControlType.FuelSystemControlTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (FuelSystemDesign.FuelSystemDesignName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (Aspiration.AspirationName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (CylinderHeadType.CylinderHeadTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (FuelType.FuelTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (IgnitionSystemType.IgnitionSystemTypeName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (Mfr.MfrName) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (EngineVersion.EngineVersion) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (Valves.ValvesPerEngine) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (BedLength.BedLength) SEPARATOR ', '),
        GROUP_CONCAT(DISTINCT (BedLength.BedLengthMetric) SEPARATOR ', ') 
    FROM
        Import_Values 
    INNER JOIN
        BaseVehicle 
            ON Import_Values.base_vehicle_id = BaseVehicle.BaseVehicleID 
    INNER JOIN
        Parts 
            ON Import_Values.part_type_id = Parts.PartTerminologyID 
    INNER JOIN
        Make 
            ON BaseVehicle.MakeID = Make.MakeID 
    INNER JOIN
        Model 
            ON BaseVehicle.ModelID = Model.ModelID 
    INNER JOIN
        Vehicle 
            ON Import_Values.base_vehicle_id = Vehicle.BaseVehicleID 
    INNER JOIN
        SubModel 
            ON Vehicle.SubModelID = SubModel.SubModelID 
    INNER JOIN
        VehicleToEngineConfig 
            ON Vehicle.VehicleID = VehicleToEngineConfig.VehicleID 
    INNER JOIN
        EngineConfig 
            ON VehicleToEngineConfig.EngineConfigID = EngineConfig.EngineConfigID 
    INNER JOIN
        EngineDesignation 
            ON EngineConfig.EngineDesignationID = EngineDesignation.EngineDesignationID 
    INNER JOIN
        EngineVIN 
            ON EngineConfig.EngineVINID = EngineVIN.EngineVINID 
    INNER JOIN
        EngineBase 
            ON EngineConfig.EngineBaseID = EngineBase.EngineBaseID 
    INNER JOIN
        FuelDeliveryConfig 
            ON EngineConfig.FuelDeliveryConfigID = FuelDeliveryConfig.FuelDeliveryConfigID 
    INNER JOIN
        FuelDeliveryType 
            ON FuelDeliveryConfig.FuelDeliveryTypeID = FuelDeliveryType.FuelDeliveryTypeID 
    INNER JOIN
        FuelDeliverySubType 
            ON FuelDeliveryConfig.FuelDeliverySubTypeID = FuelDeliverySubType.FuelDeliverySubTypeID 
    INNER JOIN
        FuelSystemControlType 
            ON FuelDeliveryConfig.FuelSystemControlTypeID = FuelSystemControlType.FuelSystemControlTypeID 
    INNER JOIN
        FuelSystemDesign 
            ON FuelDeliveryConfig.FuelSystemDesignID = FuelSystemDesign.FuelSystemDesignID 
    INNER JOIN
        Aspiration 
            ON EngineConfig.AspirationID = Aspiration.AspirationID 
    INNER JOIN
        CylinderHeadType 
            ON EngineConfig.CylinderHeadTypeID = CylinderHeadType.CylinderHeadTypeID 
    INNER JOIN
        FuelType 
            ON EngineConfig.FuelTypeID = FuelType.FuelTypeID 
    INNER JOIN
        IgnitionSystemType 
            ON EngineConfig.IgnitionSystemTypeID = IgnitionSystemType.IgnitionSystemTypeID 
    INNER JOIN
        Mfr 
            ON EngineConfig.EngineMfrID = Mfr.MfrID 
    INNER JOIN
        EngineVersion 
            ON EngineConfig.EngineVersionID = EngineVersion.EngineVersionID 
    INNER JOIN
        Valves 
            ON EngineConfig.ValvesID = Valves.Valvesid 
    INNER JOIN
        VehicleToBedConfig 
            ON Vehicle.VehicleID = VehicleToBedConfig.VehicleID 
    INNER JOIN
        BedConfig 
            ON VehicleToBedConfig.BedConfigID = BedConfig.BedConfigID 
    INNER JOIN
        BedLength 
            ON BedConfig.BedLengthID = BedLength.BedLengthID 
    GROUP BY
        Import_Values.part_id 
    ORDER BY
        NULL

Related Articles



* original question posted on StackOverflow here.