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

Too inefficient query plan for more complex JOIN/SUBQUERY conditions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.32
    • Fix Version/s: 5.5.33
    • Component/s: None
    • Labels:
    • Environment:
      Windows 7 64bit, PHP 5.3.8

      Description

      SQL:

      SELECT `i`.`id_inzerat`, `ud_boss`.`id_udalost`, `ud_boss`.`id_udalost_akce` AS `boss_akce`, `ud_makler`.`id_udalost`, `ud_makler`.`id_udalost_akce` AS `makler_akce`
      FROM `inzerat` AS `i`
      LEFT JOIN `detail` AS `d` ON d.id_detail = i.id_detail
      LEFT JOIN `nabidka` AS `n` ON n.id_nabidka = d.id_nabidka
      LEFT JOIN `udalost` AS `ud_boss` ON (ud_boss.id_nabidka = n.id_nabidka OR ud_boss.id_inzerat = i.id_inzerat) AND ud_boss.hotovo = 0 AND ud_boss.id_udalost_akce IN (18)
      LEFT JOIN `udalost` AS `ud_makler` ON (ud_makler.id_nabidka = n.id_nabidka OR ud_makler.id_inzerat = i.id_inzerat) AND ud_makler.hotovo = 0 AND ud_makler.id_udalost_akce IN (4,5,8,9,10)
      WHERE (i.id_stav IN (1, 2, 6)) AND (i.public = 1)
      GROUP BY `i`.`id_inzerat`
      ORDER BY `i`.`datum` DESC

      There is huge performance difference between MySQL (>1s) and MariaDB (>60s). The problem is in here "ud_makler.id_udalost_akce IN (4,5,8,9,10)" - if changed to for example "ud_makler.id_udalost_akce IN (4)" than the query has roughly same speed as MySQL. Including explains fot the query above for MySQL (5.5.16) and MariaDB.

      I hope this was not reported yet...

        Attachments

        1. Explain-MariaDB.png
          Explain-MariaDB.png
          18 kB
        2. Explain-MySql.png
          Explain-MySql.png
          17 kB
        3. test.sql
          2.73 MB

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            starkskalle stark skalle
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.