I am trying to eliminate a query that we need to execute for internal purposes. This query is not part of the application, so it should not be so efficient, but I would like it to be as fast as possible, so that we can get the data set that we need to move forward.
The first problem that I came up with was that the request was fixated on the status of statistics. This has been fixed by adding the following to our mySQL configuration ...
optimizer_search_depth = 0
Now the request goes directly to "Copying data to the tmp table", which has been going on for more than 5 minutes. Our server is extremely powerful, and we have 128 GB of RAM, so I do not think that this is a problem with resources. I believe that the request itself may need to be slightly optimized, and that I would like a guide, if at all possible.
The results of "EXPLAIN SELECT" can be seen here . Before running EXPLAIN SELECT, I performed "ANALYZE" for all the tables in this database (created for this one-time query).
The request itself is here:
SELECT DISTINCT
IAN.sku,
IAN.notes,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
SubModel.SubModelName,
CONCAT(EngineBase.Cylinders, ' Cyl ', EngineBase.Liter, 'L') as engine,
Positions.position,
BedLength.BedLength,
BedLength.BedLengthMetric,
BedType.BedTypeName,
BodyNumDoors.BodyNumDoors,
BodyType.BodyTypeName,
FrontBrakeType.BrakeTypeName,
RearBrakeType.BrakeTypeName,
BrakeSystem.BrakeSystemName,
BrakeABS.BrakeABSName,
DriveType.DriveTypeName,
EngineDesignation.EngineDesignationName,
EngineVIN.EngineVINName,
Valves.ValvesPerEngine,
EngineBase.Liter,
EngineBase.CC,
EngineBase.CID,
EngineBase.Cylinders,
EngineBase.BlockType,
EngineBase.EngBoreIn,
EngineBase.EngBoreMetric,
EngineBase.EngStrokeIn,
EngineBase.EngStrokeMetric,
FuelDeliveryType.FuelDeliveryTypeName,
FuelDeliverySubType.FuelDeliverySubTypeName,
FuelSystemControlType.FuelSystemControlTypeName,
FuelSystemDesign.FuelSystemDesignName,
Aspiration.AspirationName,
CylinderHeadType.CylinderHeadTypeName,
FuelType.FuelTypeName,
IgnitionSystemType.IgnitionSystemTypeName,
EngineMfr.MfrName,
EngineVersion.EngineVersion,
PowerOutput.HorsePower,
PowerOutput.KilowattPower,
MfrBodyCode.MfrBodyCodeName,
SpringType.SpringTypeName,
SteeringType.SteeringTypeName,
SteeringSystem.SteeringSystemName,
TransmissionType.TransmissionTypeName,
TransmissionNumSpeeds.TransmissionNumSpeeds,
TransmissionMfrCode.TransmissionMfrCode,
TransElecControlled.ElecControlled,
TransmissionMfr.MfrName,
WheelBase.WheelBase,
WheelBase.WheelBaseMetric
FROM
Import_AcesApplication_New IAN
INNER JOIN BaseVehicle
ON IAN.base_vehicle_id = BaseVehicle.BaseVehicleID
INNER JOIN Make
ON BaseVehicle.MakeID = Make.MakeID
INNER JOIN Model
ON BaseVehicle.ModelID = Model.ModelID
INNER JOIN Positions
ON IAN.position_id = Positions.PositionID
INNER JOIN Parts
ON IAN.part_type_id = Parts.PartTerminologyID
INNER JOIN Vehicle
ON IAN.base_vehicle_id = Vehicle.BaseVehicleID
INNER JOIN SubModel
ON Vehicle.SubModelID = SubModel.SubModelID
INNER JOIN VehicleConfig
ON Vehicle.VehicleID = VehicleConfig.VehicleID
INNER JOIN EngineConfig
ON VehicleConfig.EngineConfigID = EngineConfig.EngineConfigID
INNER JOIN EngineBase
ON EngineConfig.EngineBaseID = EngineBase.EngineBaseID
INNER JOIN EngineDesignation
ON EngineConfig.EngineDesignationID = EngineDesignation.EngineDesignationID
INNER JOIN EngineVIN
ON EngineConfig.EngineVINID = EngineVIN.EngineVINID
INNER JOIN Valves
ON EngineConfig.ValvesID = Valves.Valvesid
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 EngineMfr
ON EngineConfig.EngineMfrID = EngineMfr.MfrID
INNER JOIN EngineVersion
ON EngineConfig.EngineVersionID = EngineVersion.EngineVersionID
INNER JOIN PowerOutput
ON EngineConfig.PowerOutputId = PowerOutput.PowerOutputId
INNER JOIN BedConfig
ON VehicleConfig.BedConfigID = BedConfig.BedConfigID
INNER JOIN BedLength
ON BedConfig.BedLengthID = BedLength.BedLengthID
INNER JOIN BedType
ON BedConfig.BedTypeID = BedType.BedTypeID
INNER JOIN BodyStyleConfig
ON VehicleConfig.BodyStyleConfigID = BodyStyleConfig.BodyStyleConfigID
INNER JOIN BodyNumDoors
ON BodyStyleConfig.BodyNumDoorsID = BodyNumDoors.BodyNumDoorsID
INNER JOIN BodyType
ON BodyStyleConfig.BodyTypeID = BodyType.BodyTypeID
INNER JOIN BrakeConfig
ON VehicleConfig.BrakeConfigID = BrakeConfig.BrakeConfigID
INNER JOIN BrakeType FrontBrakeType
ON BrakeConfig.FrontBrakeTypeID = FrontBrakeType.BrakeTypeID
INNER JOIN BrakeType RearBrakeType
ON BrakeConfig.RearBrakeTypeID = RearBrakeType.BrakeTypeID
INNER JOIN BrakeSystem
ON BrakeConfig.BrakeSystemID = BrakeSystem.BrakeSystemID
INNER JOIN BrakeABS
ON BrakeConfig.BrakeABSID = BrakeABS.BrakeABSID
INNER JOIN DriveType
ON VehicleConfig.DriveTypeID = DriveType.DriveTypeID
INNER JOIN MfrBodyCode
ON VehicleConfig.MfrBodyCodeID = MfrBodyCode.MfrBodyCodeID
INNER JOIN SpringType
ON VehicleConfig.SpringTypeConfigID = SpringType.SpringTypeID
INNER JOIN SteeringConfig
ON VehicleConfig.SteeringConfigID = SteeringConfig.SteeringConfigID
INNER JOIN SteeringType
ON SteeringConfig.SteeringConfigID = SteeringType.SteeringTypeID
INNER JOIN SteeringSystem
ON SteeringConfig.SteeringSystemID = SteeringSystem.SteeringSystemID
INNER JOIN Transmission
ON VehicleConfig.TransmissionID = Transmission.TransmissionID
INNER JOIN TransmissionBase
ON Transmission.TransmissionBaseID = TransmissionBase.TransmissionBaseID
INNER JOIN TransmissionType
ON TransmissionBase.TransmissionTypeID = TransmissionType.TransmissionTypeID
INNER JOIN TransmissionNumSpeeds
ON TransmissionBase.TransmissionNumSpeedsID = TransmissionNumSpeeds.TransmissionNumSpeedsID
INNER JOIN TransmissionControlType
ON TransmissionBase.TransmissionControlTypeID = TransmissionControlType.TransmissionControlTypeID
INNER JOIN TransmissionMfrCode
ON Transmission.TransmissionMfrCodeID = TransmissionMfrCode.TransmissionMfrCodeID
INNER JOIN ElecControlled TransElecControlled
ON Transmission.TransmissionElecControlledID = TransElecControlled.ElecControlledID
INNER JOIN Mfr TransmissionMfr
ON Transmission.TransmissionMfrID = TransmissionMfr.MfrID
INNER JOIN WheelBase
ON VehicleConfig.WheelbaseID = WheelBase.WheelBaseID
LIMIT
0,10
JOINS - . -, . , , , , , . , , .
, , .