* Andreas Ahlenstorf > Roger Baklund schrieb am Freitag, 17. Oktober 2003 um 14:24: > > > SELECT IF(ISNULL(d.ProdID),d2.status, d.status) AS status > > FROM Picklists AS a > > LEFT JOIN PicklistData AS b ON > > a.ID = b.Picklist > > > LEFT JOIN ProdID AS c ON > > b.EAN = c.EAN > > LEFT JOIN EncodingData AS d ON > > c.StationID = d.StationID AND > > c.ProdID = d.ProdID > > > LEFT JOIN Trackdata AS c2 ON > > b.ISRC = c2.ISRC > > LEFT JOIN EncodingData AS d2 ON > > c2.StationID = d2.StationID AND > > c2.ProdID = d2.ProdID > > > WHERE a.ID = '1' > > Hum... The query takes something around 30 seconds and returns more or > less 3 mio rows with the same result. It should return only one > row. :(
ok, then something is obviously wrong. :) The ISRC column of the Tracdata table is not indexed, it probably should be, if you want to join on it. I see the primary keys for EncodingData and Trackdata includes MediaNr. That could be a problem, try adding "AND c2.MediaNr = d2.MediaNr" for the last join. Try selecting some columns from each table, to see what is going on: SELECT a.ID,b.EAN,b.ISRC,c.StationID,d.status,c2.StationID,d2.status ... Also, try using EXPLAIN on your query to see if/what indexes are used. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
