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 ASC

     Thankfully 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.

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.