I have an SQL command I am working on and am a little stumped. Using MySQL.
The command works great at 0 seconds execution time. SELECT i2.OV_ID1 AS OV_ID, p3.OP_ID, i2.OV_ID2, p4.OP_ID AS OP_ID2, i2.InSKU AS OV_SKU, i2.InPartNumber AS OV_Part_Number, concat (o9.OVWDDescription,'/',o10.OVWDDescription) AS OV_Name, o10.OVWDDescription as OV_Name_Option, o6.OV_Price1, o6.OV_Price1 AS OVPrice, o6.OV_Cmsn1 AS OVCmsn, o6.OV_Better_Value, o7.OV_Price1, o7.OV_Price1 AS OVPrice, o6.OV_Price1 + o7.OV_Price1 AS OVPrice10, i8.ImageID, o9.OVWDDescription, i8.ImageFileName, i2.InOwnerAvailable, i2.InOwnerAvailable2, i2.InOwnerAvailable3, i2.InVendorWh1, i2.InVendorWh2, i2.InVendorWh3, i2.InVendorWh4, i2.InVendorWh5, i2.InVendorWh6, i2.InVendorWh7, i2.InVendorWh1PO, i2.InVendorWh2PO, i2.InVendorWh3PO, i2.InVendorWh4PO, i2.InVendorWh5PO, i2.InVendorWh6PO, i2.InVendorWh7PO, i2.InVendorWh1PODate, i2.InVendorWh2PODate, i2.InVendorWh3PODate, i2.InVendorWh4PODate, i2.InVendorWh5PODate, i2.InVendorWh6PODate, i2.InVendorWh7PODate FROM myunsshared.inventory i2 LEFT JOIN myunsshared.ProductOptions p3 ON p3.OP_ID = '27674' LEFT JOIN myunsshared.ProductOptions p4 ON p4.OP_ID = '27675' LEFT JOIN myunsshared.OptionValue o6 ON o6.OV_ID = i2.OV_ID1 LEFT JOIN myunsshared.OptionValue o7 ON o7.OV_ID = i2.OV_ID2 LEFT JOIN myunsshared.Image i8 ON i8.ImageID = O6.ImageID LEFT JOIN myunsshared.OVWebDisplay o9 ON o9.OVWDID = O6.OVWDID LEFT JOIN myunsshared.OVWebDisplay o10 ON o10.OVWDID = O7.OVWDID LEFT JOIN myunsshared.PODetail p12 ON ( (p12.PODQtyOrdered > p12.PODQtyReceived) AND (p12.Ord_ID IS NULL) AND (p12.PODDateClosed = 0)) WHERE i2.P_ID = '24074' ; When I add the code in red, it jumps to 16 to 20 seconds execution time. Both p12.PODPartNumber and i2.InPartNumber have "TINYTEXT" datatype with index length of 20. Any ideas that would help? SELECT i2.OV_ID1 AS OV_ID, p3.OP_ID, i2.OV_ID2, p4.OP_ID AS OP_ID2, i2.InSKU AS OV_SKU, i2.InPartNumber AS OV_Part_Number, concat (o9.OVWDDescription,'/',o10.OVWDDescription) AS OV_Name, o10.OVWDDescription as OV_Name_Option, o6.OV_Price1, o6.OV_Price1 AS OVPrice, o6.OV_Cmsn1 AS OVCmsn, o6.OV_Better_Value, o7.OV_Price1, o7.OV_Price1 AS OVPrice, o6.OV_Price1 + o7.OV_Price1 AS OVPrice10, i8.ImageID, o9.OVWDDescription, i8.ImageFileName, i2.InOwnerAvailable, i2.InOwnerAvailable2, i2.InOwnerAvailable3, i2.InVendorWh1, i2.InVendorWh2, i2.InVendorWh3, i2.InVendorWh4, i2.InVendorWh5, i2.InVendorWh6, i2.InVendorWh7, i2.InVendorWh1PO, i2.InVendorWh2PO, i2.InVendorWh3PO, i2.InVendorWh4PO, i2.InVendorWh5PO, i2.InVendorWh6PO, i2.InVendorWh7PO, i2.InVendorWh1PODate, i2.InVendorWh2PODate, i2.InVendorWh3PODate, i2.InVendorWh4PODate, i2.InVendorWh5PODate, i2.InVendorWh6PODate, i2.InVendorWh7PODate, p12.PODQtyOrdered AS POOrder, p12.PODQtyCommitted AS POCommitted FROM myunsshared.inventory i2 LEFT JOIN myunsshared.ProductOptions p3 ON p3.OP_ID = '27674' LEFT JOIN myunsshared.ProductOptions p4 ON p4.OP_ID = '27675' LEFT JOIN myunsshared.OptionValue o6 ON o6.OV_ID = i2.OV_ID1 LEFT JOIN myunsshared.OptionValue o7 ON o7.OV_ID = i2.OV_ID2 LEFT JOIN myunsshared.Image i8 ON i8.ImageID = O6.ImageID LEFT JOIN myunsshared.OVWebDisplay o9 ON o9.OVWDID = O6.OVWDID LEFT JOIN myunsshared.OVWebDisplay o10 ON o10.OVWDID = O7.OVWDID LEFT JOIN myunsshared.PODetail p12 ON ((p12.PODPartNumber = i2.InPartNumber) AND (p12.PODQtyOrdered > p12.PODQtyReceived) AND (p12.Ord_ID IS NULL) AND (p12.PODDateClosed = 0)) WHERE i2.P_ID = '24074' ; Steve Internet Commerce Solutions ---------------------------------------- To unsubscribe from this list, please send an email to lists...@terascript.com with "unsubscribe terascript-talk" in the body.
