双表排序查询优化：已购商品优先的Top10商品列表提速方案

Hey there! Let's break down how to optimize this query so it runs faster and avoids full table scans. First, let's recap the core requirement: we need a Top10 product list where products the current user has purchased show up first, followed by the rest sorted by product name.

The Root Cause of Full Table Scans #

Your original query is scanning the entire products table likely because it's either:

• Not using indexes to quickly identify which products the user bought, or
• Performing a full-table calculation to determine purchase status for every product.

Step-by-Step Optimization #

1. Add Targeted Indexes

Indexes are the first line of defense against full scans. We need two key indexes:

• For the purchases table: Create a composite index on (user_id, product_id) to instantly find all products a user has purchased, no full scan needed:

  CREATE INDEX idx_purchases_user_product ON purchases(user_id, product_id);
  

• For the products table: Add an index on product_name to speed up the secondary sort (since we're ordering by name after purchase status):

  CREATE INDEX idx_products_name ON products(product_name);
  

2. Optimized Query Options

We have two efficient approaches to get the desired sorted list, both avoiding full table scans:

Option 1: Use EXISTS for Purchase Status (Semi-Join, Often Faster)

EXISTS uses a semi-join, which stops searching as soon as it finds a match for the user's purchase. This is super efficient with our new index:

DECLARE @UserId INT = 1; -- Replace with your user ID variable

SELECT product_id, product_name
FROM products
ORDER BY
    -- Prioritize purchased products (0 = purchased, 1 = not purchased)
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM purchases 
            WHERE user_id = @UserId AND product_id = products.product_id
        ) THEN 0 
        ELSE 1 
    END,
    product_name
LIMIT 10;

Option 2: Use LEFT JOIN with a Distinct Subquery

If you prefer a join-based approach, pre-fetch the user's purchased products first (using the index) then join to mark status:

DECLARE @UserId INT = 1;

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN (
    -- Get only unique product IDs the user bought (indexed lookup)
    SELECT DISTINCT product_id 
    FROM purchases 
    WHERE user_id = @UserId
) user_purchases ON p.product_id = user_purchases.product_id
ORDER BY
    -- Purchased products come first
    CASE WHEN user_purchases.product_id IS NOT NULL THEN 0 ELSE 1 END,
    p.product_name
LIMIT 10;

Why These Work #

• Both queries avoid full scans of products by using the product_name index for sorting (if the database can leverage it) and the purchases index to quickly check purchase status.
• The LIMIT 10 clause ensures we stop processing as soon as we have our top 10 results, saving unnecessary computation.

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