CODEWITHSUNDEEP
mysqlperformanceindexing
Maya
Maya

Reputation: 81

Query optimizer not using an index

I have two tables CUSTOMER_ORDER_PUBLIC and LINEITEM_PUBLIC which have the following indices:

+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| CUSTOMER_ORDER_PUBLIC |          1 | O_ORDERKEY    |            1 | O_ORDERKEY    | A         |     2633457 |     NULL | NULL   | YES  | BTREE      |         |               |
| CUSTOMER_ORDER_PUBLIC |          1 | O_ORDERDATE   |            1 | O_ORDERDATE   | A         |        2350 |     NULL | NULL   | YES  | BTREE      |         |               |
| CUSTOMER_ORDER_PUBLIC |          1 | PUB_C_CUSTKEY |            1 | PUB_C_CUSTKEY | A         |      273000 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

and:

+-----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name             | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| LINEITEM_PUBLIC |          0 | PRIMARY              |            1 | PUB_L_ORDERKEY   | A         |    16488602 |     NULL | NULL   |      | BTREE      |         |               |
| LINEITEM_PUBLIC |          0 | PRIMARY              |            2 | PUB_L_LINENUMBER | A         |    44146904 |     NULL | NULL   |      | BTREE      |         |               |
| LINEITEM_PUBLIC |          1 | LINEITEM_PRIVATE_FK2 |            1 | PUB_L_PARTKEY    | A         |     2083757 |     NULL | NULL   |      | BTREE      |         |               |
| LINEITEM_PUBLIC |          1 | LINEITEM_PRIVATE_FK3 |            1 | PUB_L_SUPPKEY    | A         |       85599 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Each time I run an Explain of a specific query I get the following:

mysql> EXPLAIN SELECT *
    FROM CUSTOMER_ORDER_PUBLIC
    LEFT OUTER JOIN LINEITEM_PUBLIC  ON O_ORDERKEY= PUB_L_ORDERKEY;
+----+-------------+-----------------------+------------+------+---------------+---------+---------+---------------------------------------+---------+----------+-------+
| id | select_type | table                 | partitions | type | possible_keys | key     | key_len | ref                                   | rows    | filtered | Extra |
+----+-------------+-----------------------+------------+------+---------------+---------+---------+---------------------------------------+---------+----------+-------+
|  1 | SIMPLE      | CUSTOMER_ORDER_PUBLIC | NULL       | ALL  | NULL          | NULL    | NULL    | NULL                                  | 2900769 |   100.00 | NULL  |
|  1 | SIMPLE      | LINEITEM_PUBLIC       | NULL       | ref  | PRIMARY       | PRIMARY | 4       | TPCH.CUSTOMER_ORDER_PUBLIC.O_ORDERKEY |       2 |   100.00 | NULL  |
+----+-------------+-----------------------+------------+------+---------------+---------+---------+---------------------------------------+---------+----------+-------+

For some reason the query optimizer is not using the index (O_ORDERKEY) even if I use a FORCE INDEX. I know a lot of people posted similar questions but I tried everything and nothing seems to help!

Any other suggestions would be greatly appreciated!

Edit: The query used is the following:

SELECT * FROM CUSTOMER_ORDER_PUBLIC
    LEFT OUTER JOIN LINEITEM_PUBLIC  ON O_ORDERKEY= PUB_L_ORDERKEY;

Upvotes: 0

Views: 68

Answers (2)

spencer7593
spencer7593

Reputation: 108480

The FORCE INDEX hint tells the optimizer that a full scan of the table is very expensive.

The most likely explanation for the observed behavior is that the optimizer thinks it needs to access every row in the table, and the index suggested in the hint is not a covering index for the query.

Based on the EXPLAIN output, we only see evidence of a single predicate on the JOIN operation. And it looks like the optimizer is choosing CUSTOMER_ORDER_PUBLIC as the driving table for the join, and using an index on the LINEITEM_PUBLIC table.

I'm not sure any of that answers the question you asked. (I'm not sure that there was a question asked.) Absent an actual SQL statement, we are just making guesses.

I have a question: Aside from the FORCE INDEX hint, why would we expect the optimizer to use a particular index? And why would that be a reasonable expectation?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

For this query:

SELECT *
FROM CUSTOMER_ORDER_PUBLIC cop LEFT OUTER JOIN
     LINEITEM_PUBLIC lp
     ON cop.O_ORDERKEY = lp.PUB_L_ORDERKEY;

For this query, you want an index on LINEITEM_PUBLIC(PUB_L_ORDERKEY). Of course, you already have this index because this is the first key in the primary key.

There is no reason to use an index on CUSTOMER_ORDER_PUBLIC, because all rows in the table are going to the result set.

Upvotes: 2

Related Questions