When database design goes seriously wrong...
-
I had just started at a company a few months ago and came across one of many queries that was rather astonishing to see. I've been holding this one back for awhile, but after reading others SQL WTF woes for a couple weeks now I decided to add to the pool. There are two important things to note about this query. 1) It joins 12 tables and the ONLY information it is returning is the product id, title, box art link, and esrb rating (overkill? perhaps.). 2) This query takes over 2 minutes to run, but thanks to MySQL's result caching nobody seemed to notice a problem until the server had to be rebooted (which in turn would cause us to open up a MySQL terminal window and let the query run so that the result set would be cached for our clients out in the field).
SELECT DISTINCT c.SomeCompanyID, c.Title, c.BoxArt, c.ESRB
FROM SomeCompany.catalog c LEFT JOIN SomeCompany.catalog_link cl ON c.SomeCompanyID = cl.SomeCompanyID
LEFT JOIN ProductDB.catalog_aggregate ca ON ca.ProductID = cl.ProductID
LEFT JOIN ProductDB.vendor v ON ca.VendorID = v.VendorID
LEFT JOIN ProductDB.service_link sl ON sl.VendorID = v.VendorID
LEFT JOIN ProductDB.service s ON sl.ServiceID = s.ServiceID
LEFT JOIN ProductDB.genre_link gl ON gl.SomeCompanyID = c.SomeCompanyID
LEFT JOIN ProductDB.genre g ON gl.GenreID = g.GenreID
LEFT JOIN gameguide.oberongoldgames ogg ON ogg.gameID = ca.VendorProductID AND v.Vendor = 'Oberon'
LEFT JOIN SomeCompany_downloadable.goldgames dgg ON dgg.gameID = ca.VendorProductID AND v.Vendor = 'SomeCompany Downloadable'
LEFT JOIN gameguide.trymediagoldgames tgg ON tgg.productid = ca.VendorProductID AND v.Vendor = 'Trymedia'
LEFT JOIN platform_link pl ON c.SomeCompanyID = pl.SomeCompanyID
LEFT JOIN platform p ON pl.PlatformID = p.PlatformID
WHERE g.GenreID <> '1'
AND ((ogg.Company = 'SomeCompany' AND ogg.Model = 'DEMO') OR (dgg.Company = 'SomeCompany' AND dgg.Model = 'DEMO') OR (tgg.Company = 'SomeCompany' AND tgg.Model = 'DEMO'))
AND s.Service = 'Downloadable'
ORDER BY c.Title ASCThankfully this code is no more.
-
Looks like someone was trying to solve "Where X in (select X from Y)" by using joins. That works, but it does destroy performance pretty effectively.