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

UNION subquery optimization

    XMLWordPrintable

Details

    Description

      The optimizer is not able to handle UNION subqueries.

      For example :

      SELECT * FROM user
      WHERE user.id IN (
      	SELECT idcontact FROM contact WHERE iduser = %s
      	UNION
      	SELECT iduser FROM contact WHERE idcontact = %s);
      

      is extremly slow, because the main query is of type ALL instead of using index (if I run two queries separately, its extremly fast).

      Here is the result of EXPLAIN :

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY user ALL NULL NULL NULL NULL 570642 Using where
      2 DEPENDENT SUBQUERY contact eq_ref PRIMARY,idcontact PRIMARY 8 const,func 1 Using index
      3 DEPENDENT UNION contact eq_ref PRIMARY,idcontact PRIMARY 8 func,const 1 Using index
      NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL  

      (NOTE: the second line give a wrong numbers of rows, it's greater for me)

      And this is the EXPLAIN for a simpler query without union :

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY contact ref PRIMARY,idcontact PRIMARY 4 const 35 Using index
      1 PRIMARY user eq_ref PRIMARY PRIMARY 4 contact.idcontact 1  

      Using two clauses joined with OR doesn't work either.

      Attachments

        Activity

          People

            alice Alice Sherepa
            yoch yoch
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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