How to speed up a complex SQL query with JOINs

Question

I am using the following query, and as you can see via the results below, it is taking 2+ hours to return the results. I'd love to know how I could speed this up -- I believe I've already built the indexes correctly but it is still taking an incredible amount of time to return this data.

EXPLAIN SELECT Import_Values.base_vehicle_id,
    Import_Values.qty,
    Import_Values.part_type_id,
    Import_Values.part_id,
    Import_Values.position_id,
    Import_Values.note,
    Parts.partterminologyname,
    BaseVehicle.YearID,
    Make.MakeName,
    Model.modelname,
    SubModel.SubModelName,
    EngineDesignation.EngineDesignationName,
    EngineVIN.EngineVINName,
    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,
    Mfr.MfrName,
    EngineVersion.EngineVersion,
    Valves.ValvesPerEngine,
    BedLength.BedLength,
    BedLength.BedLengthMetric,
    BedType.BedTypeName
    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 VehicleConfig
        ON Vehicle.VehicleID=VehicleConfig.VehicleID
    INNER JOIN EngineConfig
        ON VehicleConfig.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 BedConfig
        ON VehicleConfig.BedConfigID=BedConfig.BedConfigID
    INNER JOIN BedLength
        ON BedConfig.BedLengthID=BedLength.BedLengthID
    INNER JOIN BedType
        ON BedConfig.BedTypeID=BedType.BedTypeID

And the results...

+----+-------------+-----------------------+--------+-----------------------+---------+---------+-----------------------------------------------------------------+--------+-------------------+
| id | select_type | table                 | type   | possible_keys         | key     | key_len | ref                                                             | rows   | Extra             |
+----+-------------+-----------------------+--------+-----------------------+---------+---------+-----------------------------------------------------------------+--------+-------------------+
|  1 | SIMPLE      | VehicleConfig         | ALL    | NULL                  | NULL    | NULL    | NULL                                                            | 171375 |                   |
|  1 | SIMPLE      | Import_Values         | ALL    | base_vehicle_id       | NULL    | NULL    | NULL                                                            |  18933 | Using join buffer |
|  1 | SIMPLE      | BedConfig             | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.VehicleConfig.BedConfigID                  |      1 |                   |
|  1 | SIMPLE      | BedType               | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.BedConfig.BedTypeID                        |      1 |                   |
|  1 | SIMPLE      | BedLength             | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.BedConfig.BedLengthID                      |      1 |                   |
|  1 | SIMPLE      | EngineConfig          | eq_ref | PRIMARY,EngineBaseID  | PRIMARY | 4       | icarcare_importfeeds.VehicleConfig.EngineConfigID               |      1 |                   |
|  1 | SIMPLE      | FuelType              | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.FuelTypeID                    |      1 |                   |
|  1 | SIMPLE      | Valves                | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.ValvesID                      |      1 |                   |
|  1 | SIMPLE      | EngineVIN             | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.EngineVINID                   |      1 |                   |
|  1 | SIMPLE      | EngineVersion         | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.EngineVersionID               |      1 |                   |
|  1 | SIMPLE      | IgnitionSystemType    | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.IgnitionSystemTypeID          |      1 |                   |
|  1 | SIMPLE      | Mfr                   | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.EngineMfrID                   |      1 |                   |
|  1 | SIMPLE      | Aspiration            | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.AspirationID                  |      1 |                   |
|  1 | SIMPLE      | FuelDeliveryConfig    | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.FuelDeliveryConfigID          |      1 |                   |
|  1 | SIMPLE      | FuelSystemDesign      | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.FuelDeliveryConfig.FuelSystemDesignID      |      1 |                   |
|  1 | SIMPLE      | FuelDeliverySubType   | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.FuelDeliveryConfig.FuelDeliverySubTypeID   |      1 |                   |
|  1 | SIMPLE      | EngineDesignation     | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.EngineDesignationID           |      1 |                   |
|  1 | SIMPLE      | EngineBase            | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.EngineBaseID                  |      1 |                   |
|  1 | SIMPLE      | CylinderHeadType      | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.EngineConfig.CylinderHeadTypeID            |      1 |                   |
|  1 | SIMPLE      | Parts                 | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.Import_Values.part_type_id                 |      1 | Using where       |
|  1 | SIMPLE      | FuelSystemControlType | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.FuelDeliveryConfig.FuelSystemControlTypeID |      1 |                   |
|  1 | SIMPLE      | BaseVehicle           | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.Import_Values.base_vehicle_id              |      1 | Using where       |
|  1 | SIMPLE      | Make                  | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.BaseVehicle.MakeID                         |      1 |                   |
|  1 | SIMPLE      | Model                 | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.BaseVehicle.ModelID                        |      1 |                   |
|  1 | SIMPLE      | Vehicle               | eq_ref | PRIMARY,BaseVehicleID | PRIMARY | 4       | icarcare_importfeeds.VehicleConfig.VehicleID                    |      1 | Using where       |
|  1 | SIMPLE      | SubModel              | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.Vehicle.SubModelID                         |      1 |                   |
|  1 | SIMPLE      | FuelDeliveryType      | eq_ref | PRIMARY               | PRIMARY | 4       | icarcare_importfeeds.FuelDeliveryConfig.FuelDeliveryTypeID      |      1 |                   |
+----+-------------+-----------------------+--------+-----------------------+---------+---------+-----------------------------------------------------------------+--------+-------------------+
27 rows in set (2 hours 39 min 30.51 sec)

Is there anything I can do? I've tried analyzing the tables, optimizing them, etc. It just seems there must be more to do than let it run for 2 hours, lol.

Answer 1

Are you sure that your query is logically correct? I know nothing about your database so I can't tell what's going on, but here is a tiny bit of the query that seems to go against the pattern set down by the rest of the lookups in the query.

Here it is:

INNER JOIN Vehicle
    ON Import_Values.base_vehicle_id=Vehicle.BaseVehicleID

This looks strange, given the rest of your query. I would have expected the ON condition to be looking up one entry in Vehicle based on Vehicle.VehicleID which is probably the indexed field in that table. Instead, it's looking it up based on Vehicle.BaseVehicleID which is probably an unindexed field.

It's entirely possible that this is logically correct. But if it's logically incorrect, it could explain the long delay. If this turns out to be the case, then your query will deliver wrong results in addition to taking too long to complete.

Edit:

What is the relationship between ImportValues, Vehicle, and VehicleConfig? Are there many VehicleConfigs for one Vehicle? Or is it vice versa?

It looks like The explain plan ended up using a plan that amounts to a Cartesian Join between ImportValues and VehicleConfig. It's very unusual for a Cartesian join to produce the desired results. In any event, you can expect a Cartesian Join to take a long, long time. Even if the query doesn't yield a Cartesian join, if the plan uses the same algorithm, it's still going to take a long, long time.

Second Edit:

There is another join condition that looks anomalous. It's the join condition under VehicleConfig.

INNER JOIN VehicleConfig
        ON Vehicle.VehicleID=VehicleConfig.VehicleID

This is a pretty good join condition, but not for the VehicleConfig table. What it says, in plain English, is that VehicleConfig determines Vehicle. Not terribly surprising given the names of the tables and columns.

But what determines VehicleConfig?

If the answer is "nothing", then it's no wonder that poor old MySQL is doing a full scan on the table, in addition to a full scan on Import_Values.

My conclusion: fix your query so that 26 of the 27 tables are determined by an FK that references the table's PK. 24 of your joins already meet this criterion. When you are finally done, and you do an explain plan, you should see only one table doing a full table scan, namely the Import_Values table.

It should run faster, and it's more likely to produce correct results.

Answer 2

In looking at your explain plan, it looks like it's fine for everything except for Import_Values and VehicleConfig. It doesn't even look like it is considering any keys for VehicleConfig and just going straight into a full table scan which is bad.

Try creating a composite index for VehicleConfig on VehicleID and BedConfigID and an index for Import_Values on base_vehicle_id and part_type_id. If they already have indexes, it may just be a matter of updating statistics which you can do with the following commands. Hope that helps.

ANALYZE TABLE VehicleConfig
ANALYZE TABLE Import_Values

Answer 3

Try to set forceplan on and run the query. Another suggesstion is to break the query into multiple queries with 6-7 table joins in each query.

Answer 4

Please try applying an Execution Plan when executing this Query and see if doesn't suggest any missing keys/ indexes.