query without syntax errors takes too long to execute - how should i optimize it?

[Greywacke]

hi there,

 

i have the following query, which is supposed to return the valid sales lead counts and average the costs and count the leads during the past 30 days.

SELECT 
	SC.text_ServiceDescription, 
	R.text_RegionDescription, 
	GROUP_CONCAT(DISTINCT S.text_SupplierName SEPARATOR ', ') AS text_SupplierNames, 
	IFNULL(SW.smallint_SuppliersWanted,0) AS bigint_SuppliersWantedAmount, 
	COUNT(DISTINCT S.bigint_SupplierID) AS bigint_PremiumCustomersCount, 
	ROUND(AVG(T.bigint_TransactionAmount),2) AS bigint_AvgCostPerLead, 
	ROUND(COUNT(DISTINCT LS.bigint_LeadID, LS.smallint_LeadOrdinal),2) AS bigint_AvgNumLeadsGen 
FROM 4_servicesuppliers SS 
LEFT JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 
LEFT JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID OR SS.bigint_RegionID = R.bigint_ParentRegionID) 
LEFT JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 
LEFT JOIN 11_supplierswanted SW ON (SS.bigint_ServiceID = SW.bigint_ServiceID AND R.bigint_RegionID = SW.bigint_RegionID) 
LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 
LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 
LEFT JOIN 25_serviceleads SL ON (SC.bigint_ServiceID = SL.bigint_ServiceID AND SL.text_LeadAttributes LIKE CONCAT("%",SA1.text_AttributeDescription," = ",SA1.text_AttributeValue,"%"))
LEFT JOIN 27_leadssent LS ON (SL.bigint_LeadID = LS.bigint_LeadID) 
LEFT JOIN 8_transactions T ON (SL.bigint_LeadID = T.bigint_LeadID AND LS.smallint_LeadOrdinal = T.smallint_LeadOrdinal) 
WHERE 
	S.smallint_SupplierStatus = 0 AND 
	IFNULL(SW.smallint_SuppliersWanted,0) > 0 AND 
	SL.timestamp_LeadCreated >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND 
	SL.text_LeadAttributes LIKE CONCAT("%",SA1.text_AttributeDescription," = ",SA1.text_AttributeValue,"%") AND 
	LS.text_Duplicates = "" 
GROUP BY 
	SS.bigint_ServiceID, 
	SW.bigint_RegionID 
ORDER BY 
	SS.bigint_ServiceID ASC, 
	R.bigint_RegionID ASC;

however - this query takes forever to execute, even longer than the system timeout in phpmyadmin! also via php... in php i have a microtimer attached which times the query above, at 1568.6739211082 seconds.

how can i optimize the query above, especially with regard to the 25_serviceleads, 27_leadssent and 8_transactions JOIN's?

i have uploaded the sql digest for the tables above to this message, to recreate this issue (removing the email addresses of course - as we do not condone spam ) to: http://performatix.co/Untapped_Potential_Income.zip

please respond if you authentically know your mysql (especially the joins!) - you are welcome to assist. i have not been able to do the course myself yet - what i do know - is all as result of a lifelong hobby.

sincerely,

Pierre "Greywacke" du Toit.

Edited December 28, 2013 by Greywacke

[Psycho]

Well, I haven't really dug into the query yet. But, I see one glaring problem based upon the schema. You don't have indexes on the necessary fields. Any field that is typically used as a lookup value (e.g. SELECT * FROM table WHERE id ='xx') or used to JOIN tables should be indexed. When looking at the table structure in MySQL you should see an icon that looks like a lightning bolt on the far right. Click it for any fields used as primary or foreign keys.

 

Second, are the LEFT joins really necessary? The reason to use a LEFT join is where there may not be any records from the second table that match up with some of the records on the left table - but you still want those records. So, in this example:

 

FROM 4_servicesuppliers SS
LEFT JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID)

 . . . is it a logic scenario that there are some records in 4_servicesuppliers which do not have any linked records in 2_servicescatalogue? If not, just use a notrmal JOIN. LEFT JOINS are much slower.

 

That's the easy things. I would have to dig through the query structure in depth to see if there is anything else that jumps out at me. But, the indexing and removal of LEFT JOINS should have a big impact.

[Barand]

I get the impression you are storing attributes like this

"size=large,colour=red,fabric=cotton"

Don't. Normalize your data and store one attribute per row eg

+-------+--------------+----------+
| item  | attribute    | value    |
+-------+--------------+----------+
|   1   |  size        | large    |
|   1   |  colour      | red      |
|   1   |  fabric      | cotton   |
+-------+--------------+----------+

Ensure the columns used in the joins are indexed plus those in the WHERE clause

[Greywacke]

thanks alot Psycho and Barand for your valued input!

i have made all the joins as JOIN now instead of LEFT JOIN - and the performance increase on the query is quite alot! it now takes  seconds to execute - merely by switching the JOIN types which is quite feasible.

as for Barand's suggestion - I see what you have said and it holds value too - however, i would need to discuss it with my client - thanks alot!

the query has changed as follows (also due to extra requirements in getting exactly what my client wanted):

SELECT 
	SC.text_ServiceDescription, 
	R.text_RegionDescription, 
	SA1.text_AttributeValue, 
	GROUP_CONCAT(DISTINCT S.text_SupplierName SEPARATOR ', ') AS text_SupplierNames, 
	IFNULL(SW.smallint_SuppliersWanted,0) AS bigint_SuppliersWantedAmount, 
	COUNT(DISTINCT S.bigint_SupplierID) AS bigint_PremiumCustomersCount, 
	ROUND(AVG(T.bigint_TransactionAmount)/COUNT(DISTINCT LS.bigint_LeadID, LS.smallint_LeadOrdinal),2) AS bigint_AvgCostPerLead, 
	ROUND(COUNT(DISTINCT LS.bigint_LeadID, LS.smallint_LeadOrdinal),2) AS bigint_AvgNumLeadsGen 
	FROM  4_servicesuppliers SS 
JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) 
JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID OR SS.bigint_RegionID = R.bigint_ParentRegionID) 
JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) 
JOIN 11_supplierswanted SW ON (SS.bigint_ServiceID = SW.bigint_ServiceID AND R.bigint_RegionID = SW.bigint_RegionID) 
JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) 
JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) 
JOIN 25_serviceleads SL ON (SC.bigint_ServiceID = SL.bigint_ServiceID AND SL.text_LeadAttributes LIKE CONCAT("%",SA0.text_AttributeDescription," = ",SA1.text_AttributeValue,"%")) 
JOIN 27_leadssent LS ON (SL.bigint_LeadID = LS.bigint_LeadID) 
JOIN 19_consumers C ON (SL.bigint_ConsumerID = C.bigint_ConsumerID AND R.bigint_RegionID = C.bigint_ConsumerRegion) 
JOIN 8_transactions T ON (SL.bigint_LeadID = T.bigint_LeadID AND LS.smallint_LeadOrdinal = T.smallint_LeadOrdinal) 
WHERE 
	S.smallint_SupplierStatus = 0 AND 
	IFNULL(SW.smallint_SuppliersWanted,0) > 0 AND 
	SL.timestamp_LeadCreated >= DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND 
	SL.text_LeadAttributes LIKE CONCAT("%",SA1.text_AttributeDescription," = ",SA1.text_AttributeValue,"%") AND 
	LS.text_Duplicates = "" 
GROUP BY 
	SS.bigint_ServiceID, 
	SW.bigint_RegionID, 
	SA1.text_AttributeValue 
ORDER BY 
	SS.bigint_ServiceID ASC, 
	R.bigint_RegionID ASC;

as for the indexes being created - they have, just a pity they are not supported on text fields in the current storage engine (which was required for some server software we had to install). the database was designed in MyISAM back when that was the best option to use for the tables - but is now using InnoDB.

sincerely,

Pierre "Greywacke" du Toit.

Edited December 29, 2013 by Greywacke