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

"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0109070232.2196e3ea_at_posting.google.com...
> 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
NOTE: cross-posting eliminated.

The use of subquerys is one of the most time-consuming thing during the extraction of data. And the logic of this thing is quite convoluted. You ask for the products of the user that request product X AND everything else, grouped by product_id... to me this look like a

SELECT PRODUCT_ID FROM VISIT GROUP BY PRODUCT_ID ORDER BY COUNT(DISTINCT USER_ID) DESC (but maybe I missed something...)

Davide Received on Fri Sep 07 2001 - 06:18:16 CDT