Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
5.5.34
-
None
-
None
-
None
-
CentOS 6.4
Linux version 2.6.32-358.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-3) (GCC) ) #1 SMP Fri Feb 22 00:31:26 UTC 2013
Description
Complex Query below completes in 2.5 minutes on MySQL 5.6 take about 5 to 6 hours (!) on MariaDB 5.5.34.
Hardware:
Intel 24 cores
RAM 23 GB
The hardware for MariaDB and MySQL are identical and the all the parameters OS, MySQL are identical.
I'm available to schedule the GoTo meeting and show the issues live. Here is the query:
SELECT * FROM ( |
SELECT |
CONCAT('t="', aggregated.withdrawalId, '" q=10003 p1="', aggregated.withdrawalId, '"') as link_ID, |
aggregated.dateStarted AS 'Date Started', |
maxLedger.eventDate AS `Last Change`, |
'Withdrawal' AS `Type`, |
maxLedger.playerUsername AS 'Player', |
maxLedger.methodName AS 'Payment Method', |
maxLedger.currencyName AS Currency, |
ROUND(completeLedger.amount / 100.0, 2) AS Amount, |
ROUND(COALESCE(feeLedger.amount, 0) / 100, 2) AS Fee, |
CASE |
WHEN maxLedger.transactionType IN ('Withdrawal Requested', 'Withdrawal Fee Reserved') THEN 'Pending Approval' |
WHEN maxLedger.transactionType = 'Withdrawal Approved' THEN 'Pending Submission' |
WHEN maxLedger.transactionType = 'Withdrawal Submitted' THEN 'In-progress' |
WHEN maxLedger.transactionType IN ('Withdrawal Completed', 'Withdrawal Fee Completed') THEN 'Completed' |
WHEN maxLedger.transactionType IN ('Withdrawal Canceled', 'Withdrawal Fee Canceled') THEN 'Canceled' |
END as Status, |
operatorInfo.`Operator ID` AS 'Operator ID', |
operatorInfo.`Operator First Name` AS 'Operator First Name', |
operatorInfo.`Operator Last Name` AS 'Operator Last Name', |
operatorInfo.`Operator Username` AS 'Operator Username', |
operatorInfo.`Operator Title` AS 'Operator Title', |
maxLedger.memo AS 'Memo' |
FROM |
(
|
SELECT withdrawalId, MIN(eventDate) AS dateStarted, MAX(ledgerId) AS lastLedgerId |
FROM common.ViewWithdrawals w |
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
GROUP BY withdrawalId |
) aggregated
|
INNER JOIN ( |
SELECT * FROM common.ViewWithdrawals w |
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
AND "2014-01-22 00:00:00" <= eventDate |
AND "2014-01-22 23:59:59" > eventDate |
) maxLedger ON aggregated.lastLedgerId = maxLedger.ledgerId |
INNER JOIN ( |
SELECT withdrawalId, transactionType, referenceId, amount FROM common.ViewWithdrawals w |
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
AND "2014-01-22 00:00:00" <= eventDate |
) completeLedger ON aggregated.withdrawalId = completeLedger.withdrawalId AND completeLedger.transactionType = 'Withdrawal Completed' |
LEFT JOIN ( |
SELECT withdrawalId, transactionType, amount FROM common.ViewWithdrawals w |
WHERE IF("all" IN ('all', 'Withdrawal'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
AND "2014-01-22 00:00:00" <= eventDate |
) feeLedger ON aggregated.withdrawalId = feeLedger.withdrawalId AND feeLedger.transactionType = 'Withdrawal Fee Completed' |
LEFT JOIN |
common.AccountLedgerAttribute ala
|
ON ala.account_ledger_id = maxLedger.ledgerId AND ala.name = 'operatorId' |
LEFT JOIN ( |
SELECT |
op.id AS 'Operator ID', |
op.firstname AS 'Operator First Name', |
op.lastname AS 'Operator Last Name', |
op.username AS 'Operator Username', |
op.title AS 'Operator Title' |
FROM |
common.Operator op
|
LEFT OUTER JOIN OperatorGroups ogs ON op.id = ogs.operator_id |
LEFT OUTER JOIN OperatorGroup og ON ogs.group_id = og.id |
GROUP BY |
op.id, op.firstname, op.lastname, op.created
|
) operatorInfo ON ala.value = operatorInfo.`Operator ID` |
UNION ALL |
SELECT |
CONCAT('t="', t.depositId, '" q=10003 p1="', t.depositId, '"') as link_ID, |
t.dateStarted AS 'Date Started', |
q.eventDate AS `Last Change`, |
'Deposit' AS `Type`, |
playerUsername AS 'Player', |
methodName AS 'Payment Method', |
currencyName AS Currency, |
ROUND(amount / 100.0, 2) AS Amount, |
'n/a' AS Fee, |
CASE |
WHEN q.transactionType = 'Deposit Started' THEN 'In-progress' |
WHEN q.transactionType = 'Deposit Canceled' THEN 'Canceled' |
WHEN q.transactionType = 'Deposit Completed' THEN 'Completed' |
END AS Status, |
operatorInfo.`Operator ID` AS 'Operator ID', |
operatorInfo.`Operator First Name` AS 'Operator First Name', |
operatorInfo.`Operator Last Name` AS 'Operator Last Name', |
operatorInfo.`Operator Username` AS 'Operator Username', |
operatorInfo.`Operator Title` AS 'Operator Title', |
q.memo AS 'Memo' |
FROM |
(
|
SELECT depositId, MIN(eventDate) AS dateStarted, MAX(ledgerId) AS lastLedgerId |
FROM common.ViewDeposits d |
WHERE IF("all" IN ('all', 'Deposit'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
GROUP BY depositId |
) t
|
INNER JOIN ( |
SELECT * FROM common.ViewDeposits d |
WHERE IF("all" IN ('all', 'Deposit'), 1, 0) = 1 |
AND IF("all" = "all", 1, methodName = "all") = 1 |
AND IF("USD" = "all", 1, currencyName = "USD") = 1 |
AND "2014-01-22 00:00:00" <= eventDate |
AND "2014-01-22 23:59:59" > eventDate |
AND transactionType = 'Deposit Completed' |
) q ON t.lastLedgerId = q.ledgerId |
LEFT JOIN |
common.AccountLedgerAttribute ala
|
ON ala.account_ledger_id = q.ledgerId AND ala.name = 'operatorId' |
LEFT JOIN ( |
SELECT |
op.id AS 'Operator ID', |
op.firstname AS 'Operator First Name', |
op.lastname AS 'Operator Last Name', |
op.username AS 'Operator Username', |
op.title AS 'Operator Title' |
FROM |
common.Operator op
|
LEFT OUTER JOIN OperatorGroups ogs ON op.id = ogs.operator_id |
LEFT OUTER JOIN OperatorGroup og ON ogs.group_id = og.id |
GROUP BY |
op.id, op.firstname, op.lastname, op.created
|
) operatorInfo ON ala.value = operatorInfo.`Operator ID` |
) p
|
ORDER BY `Last Change` |