MariaDB / MySQL: Index hint ignored when there is left join

Question

Consider there are 2M records in both shipment and order table.

SELECT DISTINCT
    s0_.id         AS id0,
    s0_.updated_at AS updated_at1
FROM
    `shipment` s0_
        LEFT JOIN `order` s1_ ON s0_.order_id = s1_.id -- These line
        LEFT JOIN `address` s2_ ON s1_.shipping_address_id = s2_.id -- These line
ORDER BY s0_.updated_at DESC
LIMIT 20 OFFSET 0

If I remove the left join MariaDB will use the index specified, why? Is there any solution?

This SQL is generated by a library and I have limited options to fix it.

Don't ask me to remove it. I know it has no use. And I think query optimizer need to think so too as it is just LEFT JOIN.

I am using MariaDB 10.1

EXPLAIN {query} and SHOW CREATE TABLE shipment and SHOW CREATE TABLE orders to see what indexes you have. — danblack
– danblack, Commented Dec 6, 2018 at 4:04
Report the LEFT JOINs as a bug in the Pagerfanta library :-) Take responsibility for the code you use. — danblack
– danblack, Commented Dec 6, 2018 at 6:54
You could fork the project and fix it. github.com/whiteoctober/Pagerfanta — Keith John Hutchison
– Keith John Hutchison, Commented Dec 6, 2018 at 7:03

danblack · Accepted Answer · 2018-12-06 04:04:33Z

1

The left join needs to find the order_id of the shipment before it orders the output.

Forcing the index is usually the wrong thing to do, even if it can discover some things.

Use a compound index of shipment(order_id, updated_at) and you won't need to force an index.

ref: compound indexes query optimization

answered Dec 6, 2018 at 4:04

danblack

14.9k2 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tom10271 Over a year ago

I think you are correct, but it is also silly to see the optimizer process this query like this as it is just LEFT JOIN

tom10271 Over a year ago

Can you check the updated question? Your solution works if there is only one direct left join but not like the updated query

tom10271 Over a year ago

When I remove DISTINCT, then it is fast. s0_.id is primary key already. Do I need to add a unique constraint? Will it works?

danblack Over a year ago

a primary key is always unique, so yes, distinct isn't needed. EXPLAIN and SHOW CREATE TABLE info please. The additional join on addresses isn't used in the result set or any aspect of filtering.

tom10271 Over a year ago

This whole query is generated by Pagerfanta

Collectives™ on Stack Overflow

MariaDB / MySQL: Index hint ignored when there is left join

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related