See below. Let me know if you have any question on how I've structure things. BTW, this is a good example of why I believe ANSI style to be clearer and easier to spot bugs in the SQL code than Theta style...
/* Query #1 Redefined with UNION to remove OR */
SELECT
gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'true'
AND gl2.general_ledger_id = 222
AND gl2.type = 'Invoiced Transfer'
AND i2.date_posted <= '2003-01-31 23:59:59'
UNION
SELECT
gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'false'
AND gl2.general_ledger_id = 131
AND gl2.type ='Prepayment'
AND i2.date_posted <= '2003-01-31 23:59:59';
/* Query #2 Redefined with ANSI instead of Theta style, with a comment */
SELECT DISTINCT
i.id
, gl.id
, i.reference_number
, IF( gl.credit > 0 , gl.credit , - gl.debit ) AS amount
, ROUND((UNIX_TIMESTAMP( '2005-08-01 23:59:59' ) - UNIX_TIMESTAMP( i.date_posted )) / 86400) AS elapsed
, gl.date
FROM location l
INNER JOIN invoices i
ON lc.invoice_id = i.id
INNER JOIN general_ledger gl
ON i.id = gl.parent_id
LEFT JOIN company c
ON l.company_id = c.id
LEFT JOIN countries co
ON l.country = co.id
CROSS JOIN location_connection lc /* I believe you are missing a join condition relating this table to location. Currently, you have only a WHERE filter for lc.location_id = '37' (for 1? row), therefore how is this table related to location? */
WHERE l.accounting_type = 'Vendor'
AND lc.location_id = '37'
AND i.posted = 'true'
AND gl.posted = 'true'
AND gl.type IN ('Invoice Net','Prepayment')
AND lc.type IN ('Invoice To','Bank Transfer')
AND gl.gl = '5'
AND i.date_posted <= '2003-01-31 23:59:59'
ORDER BY
c.name
, l.city
, gl.date limit 11;
/*
Query #3 Redefined with ANSI instead of Theta style, with derived tables
and use of COALESCE to alleviate any need for correlated subqueries.
The derived tables will do a single lookup for prepayments, and
a single lookup for invoice transfers, alleviating both the
need for repeated queries, and alleviating the problem of no
index being used because of the join condition.
*/
SELECT DISTINCT
i.id
, gl.id
, i.reference_number
, IF( gl.credit > 0 , gl.credit , - gl.debit ) AS amount
, COALESCE(invoiced_transfers.debit, prepayments.debit) as payment
, ROUND((UNIX_TIMESTAMP( '2005-08-01 23:59:59' ) - UNIX_TIMESTAMP( i.date_posted ))/ 86400) AS elapsed
, gl.date
FROM location l
INNER JOIN invoices i
ON lc.invoice_id = i.id
INNER JOIN general_ledger gl
ON i.id = gl.parent_id
LEFT JOIN company c
ON l.company_id = c.id
LEFT JOIN countries co
ON l.country = co.id
LEFT JOIN
(
SELECT
gl2.general_ledger_id, gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'true'
AND gl2.type = 'Invoiced Transfer'
AND i2.date_posted <= '2003-01-31 23:59:59'
) AS invoiced_transfers
ON gl.id = invoiced_transfers.general_ledger_id
LEFT JOIN
(
SELECT
gl2.general_ledger_id, gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'false'
AND gl2.type = 'Prepayment'
AND i2.date_posted <= '2003-01-31 23:59:59'
) AS prepayments
ON i.id = prepayments.general_ledger_id
CROSS JOIN location_connection lc /* Still missing join condition. See below (Query #4)... */
WHERE l.accounting_type = 'Vendor'
AND lc.location_id = '37'
AND i.posted = 'true'
AND gl.posted = 'true'
AND gl.type IN ('Invoice Net','Prepayment')
AND lc.type IN ('Invoice To','Bank Transfer')
AND gl.gl = '5'
AND i.date_posted <= '2003-01-31 23:59:59'
ORDER BY
c.name
, l.city
, gl.date limit 11;
/*
Query #4. I believe this final one is what you're actually looking to do.
I believe you have forgotten to join the location table through the location connection
table. The only reason your query is working is because the location connection
table is returning only one row, and therefore the result "seems" to work correctly.
In actuality, MySQL does not know how to relate the location table to the
location_connection table, and thus, the execution plan is thrown for a loop.
Below, I take the above query and, based on your schema, put in a join condition
relating the location and location_connection tables the way I *think* it should
be done (just guessing though).
The below query should run in less than a second with the proper indexes on the tables.
*/
SELECT DISTINCT
i.id
, gl.id
, i.reference_number
, IF( gl.credit > 0 , gl.credit , - gl.debit ) AS amount
, COALESCE(invoiced_transfers.debit, prepayments.debit) as payment
, ROUND((UNIX_TIMESTAMP( '2005-08-01 23:59:59' ) - UNIX_TIMESTAMP( i.date_posted ))/ 86400) AS elapsed
, gl.date
FROM location l
INNER JOIN location_connection lc
ON l.id = lc.location_id
INNER JOIN invoices i
ON lc.invoice_id = i.id
INNER JOIN general_ledger gl
ON i.id = gl.parent_id
LEFT JOIN company c
ON l.company_id = c.id
LEFT JOIN countries co
ON l.country = co.id
LEFT JOIN
(
SELECT
gl2.general_ledger_id, gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'true'
AND gl2.type = 'Invoiced Transfer'
AND i2.date_posted <= '2003-01-31 23:59:59'
) AS invoiced_transfers
ON gl.id = invoiced_transfers.general_ledger_id
LEFT JOIN
(
SELECT
gl2.general_ledger_id, gl2.debit
FROM general_ledger gl2
INNER JOIN invoices i2
ON gl2.parent_id = i2.id
WHERE i2.posted = 'false'
AND gl2.type = 'Prepayment'
AND i2.date_posted <= '2003-01-31 23:59:59'
) AS prepayments
ON i.id = prepayments.general_ledger_id
WHERE l.accounting_type = 'Vendor'
AND lc.location_id = '37'
AND i.posted = 'true'
AND gl.posted = 'true'
AND gl.type IN ('Invoice Net','Prepayment')
AND lc.type IN ('Invoice To','Bank Transfer')
AND gl.gl = '5'
AND i.date_posted <= '2003-01-31 23:59:59'
ORDER BY
c.name
, l.city
, gl.date limit 11;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com