如何优化含INNER JOIN、DISTINCT与WHERE的SQL查询以提升性能？

Hey there! Let's dig into your query to figure out if it's running as efficiently as possible, or if we can give it a performance boost. First, let's restate your original query for clarity:

SELECT DISTINCT options.id, options.foo_option_id, options.description 
FROM vehicles 
INNER JOIN vehicle_options ON vehicle_options.vehicle_id = vehicles.id 
INNER JOIN options ON options.id = vehicle_options.option_id 
INNER JOIN discounted_vehicles ON vehicles.id = discounted_vehicles.vehicle_id 
WHERE discounted_vehicles.discount_id = 4;

Chances are, no—unless your tables are tiny or you already have all the perfect indexes in place. 1.7 seconds to return 2,067 rows is manageable, but we can almost always squeeze better performance out of join-heavy queries like this. Let's go through the most impactful tweaks.

Optimization Steps #

1. Add Targeted Indexes (Biggest Win!) #

Indexes are the #1 fix for slow join/filter queries. Here's what you should check:

• discounted_vehicles: You're filtering on discount_id = 4 and joining on vehicle_id. Create a composite index on (discount_id, vehicle_id)—this lets the database instantly find all vehicles linked to discount 4 without scanning the entire table.
• vehicle_options: This table joins on both vehicle_id and option_id. A composite index on (vehicle_id, option_id) (or reverse, depending on which column has higher uniqueness) will speed up joins to both vehicles and options.
• options: If id is already the primary key (which it should be), it's already indexed—no action needed here. Double-check that id is a PK or has a unique index to confirm.
• vehicles: Assuming id is the primary key, this is already indexed, so the join from discounted_vehicles to vehicles should be fast.

2. Ditch DISTINCT If You Can #

Wait—why are you using DISTINCT? Is the same options row showing up multiple times because one option is linked to multiple discounted vehicles? If duplicates are unavoidable, DISTINCT is necessary, but it adds overhead (the database has to sort and deduplicate rows).

A better alternative is to use EXISTS instead of joining all tables, which checks if an option is linked to any discounted vehicle without generating duplicates:

SELECT options.id, options.foo_option_id, options.description 
FROM options 
WHERE EXISTS (
    SELECT 1 
    FROM vehicle_options 
    INNER JOIN vehicles ON vehicles.id = vehicle_options.vehicle_id 
    INNER JOIN discounted_vehicles ON discounted_vehicles.vehicle_id = vehicles.id 
    WHERE vehicle_options.option_id = options.id 
      AND discounted_vehicles.discount_id = 4
);

This often runs faster because it avoids the deduplication step entirely.

3. Check the Execution Plan #

The only way to know for sure where the bottleneck is is to look at your database's execution plan:

• In PostgreSQL: Run EXPLAIN ANALYZE before your query.
• In MySQL: Use EXPLAIN FORMAT=JSON or plain EXPLAIN.

Look for red flags like:

• Full table scans (marked as Seq Scan in Postgres, ALL in MySQL) on any table—this means no index is being used.
• High row counts in the plan vs. actual rows returned—this means the database is processing way more data than needed.
• Sort operations tied to DISTINCT (like Sort in Postgres)—this is the deduplication overhead we can avoid with EXISTS.

4. Check Data Distribution #

If discount_id = 4 matches a huge number of vehicles, that'll slow things down. But since you're only returning 2k options, the join to options is probably filtering most of that out. Even so, the composite index on discounted_vehicles is critical if this discount covers a lot of rows.

When Is It "Optimal"? #

Once you've added the right indexes, removed unnecessary DISTINCT (if possible), and the execution plan shows no full table scans, you're likely at or near optimal. Aim for sub-500ms execution time (depending on your hardware/database)—that's a reasonable target for this query.

内容的提问来源于stack exchange，提问作者fabriciofreitag