[MDEV-32784] Perfromance Degradation of Joins on VIEWs after v10.4.22 - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4.31, 10.11.5, 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Fix Version/s: 10.5, 10.6, 10.11, 11.4
Component/s: None
Labels:
- regression
Environment:
RHEL 8

Description

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

20231115--feedback--01.txt
8 kB
2023-11-15 13:16
20240210--analysis--MariaDB-10.11.5.txt
16 kB
2024-02-10 01:05
20240210--analysis--MariaDB-10.4.22.txt
11 kB
2024-02-10 01:05

Issue Links

is caused by

MDEV-27510 Query returns wrong result when using split optimization

Closed

Activity

Ascending order - Click to sort in descending order

Amil Waduwawara created issue - 2023-11-12 01:55

Amil Waduwawara made changes - 2023-11-12 01:56

Field	Original Value	New Value
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 * 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.	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 * 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.

Amil Waduwawara made changes - 2023-11-12 01:57

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 * 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.

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 * 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.

Amil Waduwawara made changes - 2023-11-12 02:00

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 * 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.

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:
{code:sql}
CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS
SELECT * 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`)
);
{code}

This has become a blocker for our application.

Amil Waduwawara made changes - 2023-11-12 02:01

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:
{code:sql}
CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS
SELECT * 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`)
);
{code}

This has become a blocker for our application.

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:
{code:sql}
CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS
SELECT * 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`)
);
{code}

This has become a blocker for our application.

Amil Waduwawara made changes - 2023-11-12 02:04

Summary

Perfromance Degradation of Joins on VIEWs after some changes

Perfromance Degradation of Joins on VIEWs after v10.4.22

Alice Sherepa made changes - 2023-11-13 11:04

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Amil Waduwawara made changes - 2023-11-15 05:32

Attachment

2023115--feedback--01.txt [ 72488 ]

Amil Waduwawara made changes - 2023-11-15 13:15

Attachment

2023115--feedback--01.txt [ 72488 ]

Amil Waduwawara made changes - 2023-11-15 13:16

Attachment

20231115--feedback--01.txt [ 72489 ]

Amil Waduwawara made changes - 2023-11-15 13:28

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:
{code:sql}
CREATE DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vRecord` AS
SELECT * 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`)
);
{code}

This has become a blocker for our application.

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:
{code:sql}
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`)
);
{code}

This has become a blocker for our application.

Alice Sherepa made changes - 2023-12-05 16:52

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Alice Sherepa made changes - 2024-01-15 14:44

Status

Open [ 1 ]

Needs Feedback [ 10501 ]

Alice Sherepa made changes - 2024-01-18 12:39

Status

Needs Feedback [ 10501 ]

Open [ 1 ]

Alice Sherepa made changes - 2024-01-18 12:40

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.2 [ 28603 ]
Fix Version/s		11.3 [ 28565 ]
Fix Version/s		11.4 [ 29301 ]

Alice Sherepa made changes - 2024-01-18 12:40

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.0 [ 28320 ]
Affects Version/s		11.1 [ 28549 ]
Affects Version/s		11.2 [ 28603 ]
Affects Version/s		11.3 [ 28565 ]
Affects Version/s		11.4 [ 29301 ]

Alice Sherepa made changes - 2024-01-18 12:40

Labels

regression

Alice Sherepa made changes - 2024-01-18 13:40

Link

This issue is caused by ~~MDEV-27510~~ [ ~~MDEV-27510~~ ]

Alice Sherepa made changes - 2024-01-18 13:53

Assignee

Igor Babaev [ igor ]

Alice Sherepa made changes - 2024-01-18 13:53

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2024-01-19 17:59

Priority

Major [ 3 ]

Critical [ 2 ]

Amil Waduwawara made changes - 2024-02-10 01:05

Attachment		20240210--analysis--MariaDB-10.4.22.txt [ 73111 ]
Attachment		20240210--analysis--MariaDB-10.11.5.txt [ 73112 ]

Julien Fritsch made changes - 2024-05-03 14:30

Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.3 [ 28565 ]

Julien Fritsch made changes - 2024-09-10 15:00

Fix Version/s

10.4 [ 22408 ]

Julien Fritsch made changes - 2024-09-10 15:26

Fix Version/s

11.1 [ 28549 ]

Julien Fritsch made changes - 2024-11-22 17:06

Fix Version/s

11.2(EOL) [ 28603 ]

Alice Sherepa made changes - 2025-01-27 18:46

Fix Version/s

11.7 [ 29815 ]

Julien Fritsch made changes - 2025-02-14 11:31

Fix Version/s

11.7(EOL) [ 29815 ]

Michael Widenius made changes - 2025-04-01 13:15

Assignee

Igor Babaev [ igor ]

Sergei Petrunia made changes - 2025-04-07 08:36

Assignee

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2025-04-07 08:36

Priority

Critical [ 2 ]

Major [ 3 ]

People

Assignee:: Sergei Petrunia

Reporter:: Amil Waduwawara

Votes:: 6 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2023-11-12 01:55

Updated:: 2025-04-07 08:36

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration