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

Perfromance Degradation of Joins on VIEWs after v10.4.22

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 10.4.31, 10.11.5, 10.4(EOL), 11.0(EOL), 11.3(EOL)
    • 10.5, 10.6, 10.11, 11.2, 11.4
    • None
    • RHEL 8

    Description

      We tried upgrading current 10.4.22 to the next version and discovered that an existing VIEW used in JOIN takes more than 45mins to return the results. But in 10.4.22 we're getting the results within 8 seconds for 25000 records. We discovered the same behavior with recent 10.4.31 and 10.11.5 too.

      Below is the VIEW we're using:

      CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS 
      SELECT
        H1.`id` AS `historyId`, H1.`uId` AS `uId`, H1.`jobTitleId` AS `jobTitleId`, H1.`joinedDate` AS `joinedDate`, 
        H1.`eDate` AS `eDate`, H1.`eventId` AS `eventId`, HE.`name` AS `eventName`
      FROM (
        (`History` H1
          LEFT JOIN `HistoryEvent` HE ON (HE.`id` = H1.`eventId`)
        ) JOIN (
          SELECT SH1.`uId` AS `uId`, MAX(SH1.`id`) AS `id`
          FROM (
            (
              SELECT `id`, `uId`, IFNULL(`eDate`, '1970-01-01') AS `eDate`
              FROM `History`
            ) SH1
            JOIN (
              SELECT TMP.`uId` AS `uId`, MAX(TMP.`eDate`) AS `eDate`
              FROM (
                SELECT `uId`, IFNULL(`eDate`, '1970-01-01') AS `eDate`
                FROM `History`
              ) TMP
              WHERE TMP.`eDate` <= curdate()
              GROUP BY TMP.`uId`
            ) SH2 ON (SH1.`uId` = SH2.`uId` and SH1.`eDate` = SH2.`eDate`)
          )
          GROUP BY SH1.`uId`
        ) H2 ON (H1.`uId` = H2.`uId` and H1.`id` = H2.`id`)
      );
      

      This has become a blocker for our application.

      Attachments

        1. 20231115--feedback--01.txt
          8 kB
          Amil Waduwawara
        2. 20240210--analysis--MariaDB-10.11.5.txt
          16 kB
          Amil Waduwawara
        3. 20240210--analysis--MariaDB-10.4.22.txt
          11 kB
          Amil Waduwawara

        Issue Links

          Activity

            People

              igor Igor Babaev
              amilwaduwawara Amil Waduwawara
              Votes:
              6 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.