Re: Performance issues of one vs. two split tables.

On 16-May-07, at 4:05 PM, PFC wrote:

> This makes queries hard to optimize. Consider the table (user_id,
> item_id) meaning user selected this item as favourite.
> If you want to know which users did select both items 1 and 2, you
> have to do a self-join, something like :
>
> SELECT... FROM favourites a, favourites b WHERE a.user_id =
> b.user_id AND a.item_id=1 AND b.item_id = 2

You could get users who have selected both items 1 and 2 without
doing a self-join with a query like the following:

select user_id from favourite where item_id = 1 or item_id = 2 group
by user_id having count(*) = 2;