How do I force a JOIN to use a specific index in MySQL?

Question

I have a query that JOINs 2 tables; lineitem and part,

select
        sum(l_extendedprice* (1 - l_discount)) as revenue
from
        lineitem force index for join (l_pk),
        part
where
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#12'
                and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                and l_quantity >= 1 and l_quantity <= 1 + 10
                and p_size between 1 and 5 
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#23'
                and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
                and l_quantity >= 10 and l_quantity <= 10 + 10
                and p_size between 1 and 10
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#34'
                and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                and l_quantity >= 20 and l_quantity <= 20 + 10
                and p_size between 1 and 15
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        );

I have an index on lineitem and want the query to use this index for the join p_partkey = l_partkey

create index l_pk on tpch.lineitem(l_partkey);

MySQL explain shows:

| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows    | Extra       |       
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | lineitem | ALL    | l_pk          | NULL    | NULL    | NULL                    | 5982534 | Using where | 
|  1 | SIMPLE      | part     | eq_ref | PRIMARY       | PRIMARY | 4       | tpch.lineitem.L_PARTKEY |       1 | Using where | 
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+

Why is the index l_pk not used?

Answer 1

Even though you are using FORCE INDEX the Query Optimizer took over as expected.

The pass through the lineitem table exceeded more than 5% of the total index entries. This is what lead to the Query Optimizer to dismiss using the index. Oracle, MSSQL and PostgreSQL would not have done any different.

Here is my sick attempt at refactoring the query

select sum(l_extendedprice* (1 - l_discount)) as revenue
from
(
    select p_partkey l_partkey,'S' contsize from part
    where p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and p_size between 1 and 5 
    union
    select p_partkey,'M' contsize from part
    where p_brand = 'Brand#23'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and p_size between 1 and 10
    union
    select p_partkey,'L' contsize from part
    where p_brand = 'Brand#34'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and p_size between 1 and 15
) partkeys
left join listitem using (l_partkey)
where
    l_shipmode in ('AIR', 'AIR REG') and
    l_shipinstruct = 'DELIVER IN PERSON' and
    IF(contsize='S',IF(l_quantity >= 1 and l_quantity <= 1 + 10,1,0),
        IF(contsize='M',IF(l_quantity >= 10 and l_quantity <= 10 + 10,1,0),
            IF(contsize='L',IF(l_quantity >= 20 and l_quantity <= 20 + 10,1,0),0)
        )
    )
;

You may need to index the tables

ALTER TABLE parts ADD INDEX brand_container_size_ndx
(p_brand,p_container,p_size,p_partkey);
ALTER TABLE listitem ADD INDEX partkey_shipinstruct_shipmode_quantity_ndx
(l_partkey,l_shipinstruct,l_shipmode ,l_quantity);

Give it a Try and let us know if it is fast or if it even works.

Answer 2

The index l_pk isn't being used because of the way tables are joined.

To make use an index, we need to have something to look up in that index.

When joining two tables, there's a value in the left table and we need to match it up with corresponding rows in the right table, so we use the index on the right table to find the matching rows -- OR, there's a value in the right table and we need to match it up with rows in the left table, so we use an index on the left table to find the matching rows.

You don't use an index on both tables to perform the join -- you use one or the other. In this case the primary key on part is being used for the join.

So, the next question is "why?"

To answer that, we consider what your query is asking the server to do.

Either of these two processes, performed by the server would generate the same result:

You're asking the server to...

1. find all the rows in lineitem based on values you've supplied for l_quantity, l_shipmode, and l_shipinstruct ... and then join to all the matching rows on part, but only those that also have the expected values for p_brand, p_container, and p_size... OR
2. find rows in part based on p_brand, p_container, and p_size and then identify the matching rows in lineitem where there are matching values for l_quantity, l_shipmode, l_shipinstruct.

Your possible_keys contain only one index each, which suggests that none of the columns you're searching ... p_brand, p_container, p_size, l_quantity, l_shipmode, l_shipinstruct ... have indexes on them.

So the server is going to have no choice but to do a full table scan on one table or the other. It's choosing lineitem because it has concluded that this will be the most cost-effective route.

The real issue appears to be that you're searching on columns that should be indexed.