Order by column in a left joined table
In a query like:
SELECT a.c1, b.c2
FROM a
LEFT JOIN b ON a.id = b.a_id
ORDER BY b.c3;
No index is used since b.c3 is a column of a left join table, so the optimizer ignores it.
We have implemented an optimization in our client code that split the query in two: first part with WHERE b.c3 IS NULL, and a second part with WHERE b.c3 IS NOT NULL. It works great, using the index in both cases.
Does MariaDB have an optimization that can handle these cases? Are there anyone planned? I have searched a lot but could not find any reference. It's a very simple problem, and there are some solutions, except that my queries are dynamically generated so the typical solution of adding a column in table "a" with a 0 value for non existant records in table "b" does not work for us.
THanks!
Jose
Question information
- Language:
- English Edit question
- Status:
- Answered
- For:
- MariaDB Edit question
- Assignee:
- No assignee Edit question
- Last query:
- Last reply:
Can you help with this problem?
Provide an answer of your own, or ask Jose Canciani for more information if necessary.