Order by column in a left joined table

Asked by Jose Canciani

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:
Revision history for this message
Sergei Golubchik (sergii) said :
#1

There is no such optimization in MariaDB, as far as I know, and not planned yet.

But you're welcome to submit a new feature request at mariadb.org/jira and we'll consider it for the next major release

Revision history for this message
Jose Canciani (u-ubuctu-b) said :
#2

Thanks for your response Sergei. We'll consider the feature request.

Revision history for this message
Jose Canciani (u-ubuctu-b) said :
#3

Just to follow up, here it is: https://mariadb.atlassian.net/browse/MDEV-6568

Can you help with this problem?

Provide an answer of your own, or ask Jose Canciani for more information if necessary.

To post a message you must log in.