Hi Peter,
Sorry I didn't understand what you meant by bbCodes. I think I do now....
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.
'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