How do I force a JOIN to use a specific index in MySQL? 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 &gt;= 1 and l_quantity &lt;= 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 &gt;= 10 and l_quantity &lt;= 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 &gt;= 20 and l_quantity &lt;= 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?
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 &gt;= 1 and l_quantity &lt;= 1 + 10,1,0),
        IF(contsize='M',IF(l_quantity &gt;= 10 and l_quantity &lt;= 10 + 10,1,0),
            IF(contsize='L',IF(l_quantity &gt;= 20 and l_quantity &lt;= 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.