Example SQLFiddle here: http://sqlfiddle.com/#!9/ba20f/2
The example only has 5 rows in each of the 2 tables, but still takes too long to load IMO. Wish it would let me insert more, which will increase time dramatically with more rows and data...
This query is taking too long to complete. The rows in wp_quantum_purchases and wp_quantum_sales are around 11,000 each. wp_hunter_quote_parts and wp_hunter_quotes tables are completely empty (as this is to store data moving forward in the system).
So, I have imported data from another system (purchases and sales) into separate tables. What I need to do is to be able to show only the IsActive = 1 parts for both purchases and sales tables, however, if wp_hunter_quote_parts table has these parts (ItemName) than it should pull the data from the wp_hunter_quote_parts and wp_hunter_quotes tables instead. What I have here is 3 FULL OUTER JOINS, but I feel that this can be done so that it doesn't take over 90 seconds to load the data. Especially, since I'm using a LIMIT on it of 40 at a time.
(SELECT IFNULL(qp.ItemName, qs.ItemName) AS name, IFNULL(qp.TimeAdded, qs.TimeAdded) AS created, qs.SalesDate AS effective, qp.VendorName AS supplier, qp.Source AS source, qp.VendorType AS type, qp.Price AS cost, qs.Price AS price, CAST((((CAST(qs.Price AS DECIMAL(10,2)) - CAST(qp.Price AS DECIMAL(10,2))) / CAST(qp.Price AS DECIMAL(10,2))) * 100) AS DECIMAL(10,2)) AS markup, qs.CustomerName AS customer, qs.CustomerListID AS customerListID, qp.VendorListID AS vendorListID, '' AS itemListID
FROM wp_quantum_purchases AS qp
LEFT JOIN wp_quantum_sales AS qs ON (qs.ItemName = qp.ItemName AND qs.IsActive = 1)
WHERE qp.IsActive = 1 AND NOT EXISTS (
SELECT 1
FROM wp_hunter_quote_parts AS hqp
WHERE qp.ItemName = hqp.ItemName AND hqp.IsActive = 1
)
)
UNION ALL
(SELECT IFNULL(qp.ItemName, qs.ItemName) AS name, IFNULL(qp.TimeAdded, qs.TimeAdded) AS created, qs.SalesDate AS effective, qp.VendorName AS supplier, qp.Source AS source, qp.VendorType AS type, qp.Price AS cost, qs.Price AS price, CAST((((CAST(qs.Price AS DECIMAL(10,2)) - CAST(qp.Price AS DECIMAL(10,2))) / CAST(qp.Price AS DECIMAL(10,2))) * 100) AS DECIMAL(10,2)) AS markup, qs.CustomerName AS customer, qs.CustomerListID AS customerListID, qp.VendorListID AS vendorListID, '' AS itemListID
FROM wp_quantum_sales AS qs
LEFT JOIN wp_quantum_purchases AS qp ON (qp.ItemName = qs.ItemName AND qp.IsActive = 1)
WHERE qs.IsActive = 1 AND NOT EXISTS (
SELECT 1
FROM wp_hunter_quote_parts AS hqp
WHERE qs.ItemName = hqp.ItemName AND hqp.IsActive = 1
)
)
UNION ALL
(SELECT hqp.ItemName AS name, hq.Quote_Date AS created, hqp.SalesDate AS effective, hqp.VendorName AS supplier, hqp.Source AS source, hqp.VendorType AS type, hqp.Cost AS cost, hqp.Price AS price, CAST((((CAST(hqp.Price AS DECIMAL(10,2)) - CAST(hqp.Cost AS DECIMAL(10,2))) / CAST(hqp.Cost AS DECIMAL(10,2))) * 100) AS DECIMAL(10,2)) AS markup, IFNULL(hq.Customer_FullName, 'N/A') AS customer, hq.Customer_ListID AS customerListID, hqp.VendorListID AS vendorListID, hqp.Item_ListID AS itemListID
FROM wp_hunter_quote_parts AS hqp
LEFT JOIN wp_hunter_quotes AS hq ON (hq.id = hqp.QuoteID)
WHERE hqp.IsActive = 1)
ORDER BY name ASC
LIMIT 0, 40;
Can anyone help me here? I need to merge both the purchases and sales tables into a combined table (which this is doing... undoubtably very slow though), and than needs to outer join against the hunter_quote_parts table so that if hqp.IsActive = 1 exists, than we need to pull in that data from the wp_hunter_quote_parts table instead of that from the combined other 2 tables.
How can I make this query work faster or change it so that it functions the same, but faster? Please anyone help?
What would be a great solution, if possible, would be to use something like this instead:
SELECT *
FROM wp_quantum_purchases AS qp, wp_quantum_sales AS qs
LEFT JOIN wp_hunter_quote_parts AS hqp ON ((hqp.ItemName = qp.ItemName || hqp.ItemName = qs.ItemName) AND hqp.IsActive = 1)
LEFT JOIN wp_hunter_quotes AS hq ON (hq.id = hqp.QuoteID)
WHERE qp.IsActive = 1 || qs.IsActive = 1;
However, the problem with this query is that it relies on wp_quantum_purchases or wp_quantum_sales to have a row in it that has IsActive = 1 before it will allow it to grab any data from wp_hunter_quote_parts or wp_hunter_quotes tables. This is an unacceptable query, as it needs to capture data moving forward in my system, and data moving forward will be captured inside of the wp_hunter_quotes and wp_hunter_quote_parts tables. The ItemName could be a new item that doesn't even exist in the quantum tables. Although if it doesn't exist in the quote tables, I need to show the previous data from wp_quantum_purchases and wp_quantum_sales if it exists. How is anyone supposed to this?? This seems very basic and should be possible to do, but why is it so difficult to understand? And than on top of it, the query that I found to work for this is taking way too long to complete. Not sure what more you need here...?
