How do I force a JOIN to use a specific index in MySQL?
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.