3 way join optimization
760601Mar 16 2010 — edited Mar 17 2010Hi,
I have a query that takes ages (~8 minutes) to run and i wanted to know if anyone can think of a way to optimize it...
SELECT I.ISBN13, I.image_uri, I.creation_date, I.last_modified_date, D.IS_UK, D.IS_AU, D.IS_DELETED_AU, D.IS_DELETED_UK
FROM (nielsen_images I inner join nielsen_to_do_run R on r.Isbn13 = I.ISBN13) inner join nielsen D on r.isbn13 = d.isbn13
WHERE R.status = 1
AND (D.IS_DELETED_UK=0 OR D.IS_DELETED_AU=0)
nielsen is a books data table containing 6.2 million records (ISBN13 is the PK)
images contains ~2 million records (one per ISBN13 which is also the PK)
nielsen_to_do_run is a table used fot bulk processing of the data and is also ~6 million records (size drops after every bulk) status=1 is only for 300 records every time. (ISBN13 is the PK)
Thanks,
Roy
BTW - I have full permissions on these table so adding indexes or whatever is a valid option (if it'll help)
Edited by: user9351627 on Mar 16, 2010 5:43 PM