Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
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
-
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
Issue Links
- is caused by
-
-
- Closed
-
Activity
Any progress on this issue? Any plans of resolving?
This is a blocker for our application and we're stuck in v10.4.22.
amilwaduwawara: the output from ANALYZE FORMAT=JSON before the upgrade and after the upgrade would help us a lot in the analysis of the problem.
Requested ANALYZE results have been attached:
Any progress on this issue? Any plans of resolving?
This is a blocker for our application and we're stuck in v10.4.22.
igor, note that there's GROUP BY tmp.id2