mysql join或php in_array（）哪个更好地优化Mysql查询？

Question

I am trying to implement a watch-list functionality to my current project,in which users can add items to their watchlist.

So up to adding the items to watch list works fine but when displaying all items(that in watchlist and not in watchlist collectively) in a particular category,I need to check whether the item in the users watchlist or not and if it is not in watchlist i have to show a button to add to watch list.....if it is already in watch list a button to remove it.

So here I had following tables

watch-list table

item-id(int)
username
date-added (to watch list)

items table
item-id(int)
item-name
item-description
date-added
users table
username
fname
lname
joinDate

At present when a user visits items page there are number of mysql queries
1 query for user check(if logged in get username at the top of the page)
1 query for showing articles(using category filters)
Many queries to show whether an item is in watch list.
I am using a query inside a loop which checks for username and item-id pair from watchlist table and grab the item-id compares that with present article and desides whether it is in watch list (I know it is a bad way ...but I used that for a start as I am a beginner)
If there are 20 items displaying per page there will be 20 queries if 30 items 30 querys and so on....So I just started to figure out ways to optimize it.

One way that I was thinking to implement is to grab the all the item-id in the watch-list table with one query to an array and check using php in_array() before printing the items to the page and print appropriately

I think this works fine for now.But as I am a beginner I want to learn best practices to optimize queries
Is this good to go or can some of you suggest other techniques.

Some of you may suggest to use joins .......but I don't know how to use them in this scenario.If it is a better way ....can some one explain me how to use joins in this scenario

Thanks in advance
Shrikanth

Answer 1

Your thoughts are right, and it would be better to use a join, as this might save you the additional queries for each item.

What you have to do now: Use a left join to determine if the entry is watchlisted. (if it is NOT, the right handed result of the join will be null.

It would look like this (untested):

Selects all Watchlisted items for current user.

SELECT 
 i.* 
FROM
 items i
LEFT JOIN
 watch-list wl
ON
  i.item-id = wl.item-id AND
  wl.user-id = {$currentUserId}
WHERE
  NOT isnull(wl.item-id) --if theres a right side, its watchlisted

Vice Versa, selects all NOT watchlisted

SELECT 
 i.* 
FROM
 items i
LEFT JOIN
 watch-list wl
ON
  i.item-id = wl.item-id AND
  wl.user-id = {$currentUserId}
WHERE
  isnull(wl.item-id) 

Selects ALL items and inserts a flag to determine wheter the item is watchlisted or not.

SELECT 
 i.*,
 IFNULL(wl.item-id, "0") as watchlisted -- will be > 0 if watchlisted.  
FROM
 items i
LEFT JOIN
 watch-list wl
ON
  i.item-id = wl.item-id AND
  wl.user-id = {$currentUserId}