Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6568

Performance improvement: Order by column in a left joined table

    XMLWordPrintable

    Details

      Description

      Originally asked in https://answers.launchpad.net/maria/+question/252779

      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 (depending on the order by ascending clause). It works great and most of the times the second part is not needed (when using limit). But it would be great if the database engine can figure it out by itself.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jose.canciani Jose Canciani
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.