Re: Optimization of query finding related products for a certain product

From: Stephen Ashmore <sashmore_at_neonramp.com>
Date: Fri, 7 Sep 2001 08:20:06 -0000
Message-ID: <tphidg5sp6gud3@corp.supernews.com>

Try changing the IN to an exists:

SELECT PRODUCT_ID FROM VISIT A WHERE
USER_ID EXISTS
   (SELECT DISTINCT USER_ID FROM VISTS WHERE PRODUCT_ID

• 1883577330 and USER_ID = A>USER_ID) AND PRODUCT_ID <> 1883577330 GROUP BY PRODUCT_ID ORDER BY COUNT(DISTINCT USER_ID) DESC

ALso Ensure you have an index on PRODUCT_ID and USER_ID

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com

"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0109070232.2196e3ea_at_posting.google.com...
> Hi everybody,
>
> This query finds the related products for a certain product based on
> user visits.
>
> Today this query takes 3 seconds. Is it possible to make it faster
> with the same result?
>
> SELECT PRODUCT_ID FROM VISIT WHERE
> USER_ID IN (SELECT DISTINCT USER_ID FROM VISIT WHERE PRODUCT_ID =
> 1883577330)
> AND PRODUCT_ID <> 1883577330 GROUP BY PRODUCT_ID ORDER BY
> COUNT(DISTINCT USER_ID) DESC
>
> This is the table:
>
> VISIT
> -----
> USER_ID INT *
> PRODUCT_ID INT *
> VISITDATE DATE *
>
> Thank you for your help!
>
> / Jacob
Received on Fri Sep 07 2001 - 03:20:06 CDT