MySQL Forums
Forum List  »  Stored Procedures

Re: Query times out when I add extra Where Condition
Posted by: John Noble
Date: January 24, 2020 05:25AM

Hi Peter,

I have extended the timeout period from 30 to 120 seconds and added an index.

I tried changing the some of the joins to INNER instead of LEFT but the query still took 92.618 seconds.

But when I set the JOINS back to LEFT, it only takes 8.440 seconds. Thats probably the reason why the JOINS were set to LEFT.

Using mysql 5.5
InnoBufferPoolSize = 107M

Here is the Explain.


id select_type table type possible_keys key key_len ref rows Extra
'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '3192', 'Using filesort'
'2', 'DERIVED', 'salesledger', 'ALL', NULL, NULL, NULL, NULL, '409700', 'Using where; Using temporary; Using filesort'
'2', 'DERIVED', 'receipts', 'ref', 'invNum', 'invNum', '4', 'sst.salesledger.invNum', '1', ''
'2', 'DERIVED', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'2', 'DERIVED', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'2', 'DERIVED', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'2', 'DERIVED', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'3', 'UNION', 'salesledger', 'ALL', NULL, NULL, NULL, NULL, '409700', 'Using where; Using temporary; Using filesort'
'3', 'UNION', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'3', 'UNION', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'3', 'UNION', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'3', 'UNION', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'3', 'UNION', 'receipts', 'ALL', 'invoiceDate', NULL, NULL, NULL, '309529', 'Using where; Using join buffer'
'4', 'UNION', 'salesledger', 'ref', 'invNum', 'invNum', '4', '', '4244', 'Using where; Using temporary; Using filesort'
'4', 'UNION', 'customers', 'ref', 'customerID', 'customerID', '9', 'sst.salesledger.customerID', '1', ''
'4', 'UNION', 'groups', 'ALL', NULL, NULL, NULL, NULL, '16', ''
'4', 'UNION', 'users', 'ALL', NULL, NULL, NULL, NULL, '56', ''
'4', 'UNION', 'countries', 'ALL', NULL, NULL, NULL, NULL, '41', ''
'4', 'UNION', 'receipts', 'ALL', 'invoiceDate,receiptDate', NULL, NULL, NULL, '309529', 'Using where; Using join buffer'
NULL, 'UNION RESULT', '<union2,3,4>', 'ALL', NULL, NULL, NULL, NULL, NULL, ''



Here is the FULL sproc I am picking up 3 sets of data and merging them ...
CREATE DEFINER=`root`@`%` PROCEDURE `usp_ADCustomerIDAll`(
IN p_date date,
IN p_freshFrozen varchar(1),
IN p_sterling bool
)
BEGIN

SELECT * FROM
(
-- Invoices and Credits WIth Balances
SELECT salesledger.customerID as customerID,
salesledger.countryID as countryID,
salesledger.invoiceDate as InvoiceDate,
salesledger.dueDate as dueDate,
salesledger.invNum as invNum,
if (p_sterling = true, "GBP", salesledger.invoiceCurrency) as invoiceCurrency,
salesledger.CCmanagerID as CCmanagerID,
if (p_sterling = true, round(salesledger.payable / salesledger.conversionRate, 2), salesledger.payable) as payable,
salesledger.paidToDate as paidToDate,
if (p_sterling = true, round(salesledger.net / salesledger.conversionRate, 2), salesledger.net) as net,
if (p_sterling = true, round(salesledger.vat / salesledger.conversionRate, 2), salesledger.vat) as vat,
if (p_sterling = true, round((salesledger.payable - ifnull(sum(CASE WHEN receiptDate <= p_date THEN allocationAmount ELSE 0 END), 0)) / salesLedger.conversionRate, 2),
salesledger.payable - ifnull(sum(CASE WHEN receiptDate <= p_date THEN allocationAmount ELSE 0 END), 0))
as balance,
salesLedger.freshFrozen as freshFrozen,
customers.name AS cname,
customers.telephone as ctelephone,
ifnull(customers.groupID, "") as cgroupID,
ifnull(groups.name, "") as groupName,
ifnull(users.name, "") as username,
countries.name as countryName,
p_date as selectedDate

FROM salesledger
LEFT JOIN receipts ON
salesledger.invnum = receipts.invnum
LEFT JOIN customers ON
salesledger.customerID = customers.customerID
LEFT JOIN groups ON
customers.groupID = groups.ID
LEFT JOIN users ON
salesledger.CCmanagerID = users.userID
LEFT JOIN countries ON
salesledger.countryID = countries.countryID


WHERE
(salesledger.docType = "Inv" or salesledger.docType = "Crd")
and salesledger.invoiceDate <= p_date
and (salesledger.paidInFullDate = '0001-01-01' or salesledger.paidInFullDate > p_date)
and salesLedger.freshFrozen <> p_freshFrozen
GROUP BY
customerID, invNum, invoiceDate, payable



UNION ALL

-- Payments WIth Balances
SELECT salesledger.customerID as customerID,
salesledger.countryID as countryID,
salesledger.invoiceDate as InvoiceDate,
salesledger.dueDate as dueDate,
salesledger.invNum as invNum,
if (p_sterling = true, "GBP", salesledger.invoiceCurrency) as invoiceCurrency,
salesledger.CCmanagerID as CCmanagerID,
if (p_sterling = true, round(salesledger.payable / salesledger.conversionRate, 2), salesledger.payable) as payable,
salesledger.paidToDate as paidToDate,
if (p_sterling = true, round(salesledger.net / salesledger.conversionRate, 2), salesledger.net) as net,
if (p_sterling = true, round(salesledger.vat / salesledger.conversionRate, 2), salesledger.vat) as vat,
if (p_sterling = true, ifnull(round((salesledger.payable + sum(receipts.allocationAmount)) / salesledger.conversionRate, 2), round(salesledger.payable / salesledger.conversionRate, 2)),
ifnull(salesledger.payable + sum(receipts.allocationAmount) , salesledger.payable)) as balance,
salesLedger.freshFrozen as freshFrozen,
customers.name AS cname,
customers.telephone as ctelephone,
ifnull(customers.groupID, "") as cgroupID,
ifnull(groups.name, "") as groupName,
ifnull(users.name, "") as username,
countries.name as countryName,
p_date as selectedDate
FROM salesledger
LEFT JOIN receipts ON
salesledger.paymentNo = receipts.paymentNo
LEFT JOIN customers ON
salesledger.customerID = customers.customerID
LEFT JOIN groups ON
customers.groupID = groups.ID
LEFT JOIN users ON
salesledger.CCmanagerID = users.userID
LEFT JOIN countries ON
salesledger.countryID = countries.countryID
WHERE
salesledger.docType = "PAY"
and salesledger.invoiceDate <= p_date
and receipts.invoiceDate <= p_date
and (salesledger.paidInFullDate = '0001-01-01' or salesledger.paidInFullDate > p_date)
and salesLedger.freshFrozen <> p_freshFrozen
GROUP BY
customerID, invNum, invoiceDate, payable



UNION ALL
-- UNallocated WIth balances
SELECT salesledger.customerID as customerID,
salesledger.countryID as countryID,
salesledger.invoiceDate as InvoiceDate,
salesledger.dueDate as dueDate,
salesledger.invNum as invNum,
if (p_sterling = true, "GBP", salesledger.invoiceCurrency) as invoiceCurrency,
salesledger.CCmanagerID as CCmanagerID,
if (p_sterling = true, round(salesledger.payable / salesledger.conversionRate, 2), salesledger.payable) as payable,
salesledger.paidToDate as paidToDate,
if (p_sterling = true, round(salesledger.net / salesledger.conversionRate, 2), salesledger.net) as net,
if (p_sterling = true, round(salesledger.vat / salesledger.conversionRate, 2), salesledger.vat) as vat,
if (p_sterling = true, round(salesledger.payable - ifnull(SUM( CASE WHEN Masterunallocated = "F" THEN allocationAmount ELSE 0 END) / salesLedger.conversionRate, salesledger.payable / salesLedger.conversionRate), 2),
salesledger.payable - ifnull(SUM( CASE WHEN Masterunallocated = "F" THEN allocationAmount ELSE 0 END), salesledger.payable)) as balance,
salesLedger.freshFrozen as freshFrozen,
customers.name AS cname,
customers.telephone as ctelephone,
ifnull(customers.groupID, "") as cgroupID,
ifnull(groups.name, "") as groupName,
ifnull(users.name, "") as username,
countries.name as countryName,
p_date as selectedDate
FROM salesledger
LEFT OUTER JOIN receipts ON
salesledger.unallocatedNo = receipts.unallocatedNo
LEFT JOIN customers ON
salesledger.customerID = customers.customerID
LEFT JOIN groups ON
customers.groupID = groups.ID
LEFT JOIN users ON
salesledger.CCmanagerID = users.userID
LEFT JOIN countries ON
salesledger.countryID = countries.countryID
WHERE
salesledger.unallocatedNo <> 0
and salesledger.invNum = -99999
and salesledger.invoiceDate <= p_date
and receipts.invoiceDate <= p_date
and receipts.receiptDate <= p_date
and (salesledger.paidInFullDate = '0001-01-01' or salesledger.paidInFullDate > p_date)
and salesLedger.freshFrozen <> p_freshFrozen
GROUP BY
customerID, invNum, invoiceDate, payable

) a

ORDER BY
countryID, customerID, invNum;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query times out when I add extra Where Condition
433
January 24, 2020 05:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.