"You might also like" suggestions

[Lapixx]

Hi,

Although I'm not really a novice when talking about web development, I'm not really used to writing MySQL queries that are a bit more advanced than the absolute basic SELECT/INSERT/UPDATE/DELETE queries.

Anyway, currently I started working on a project that shows relevant items to the user: A 'you might also like' screen, or another example, a 'people who bought this product also purchased this product' function. In my case, the user can 'favorite' an item and see which other items might be interesting to him/her.

To achieve this, I came up with the following query:

SELECT item
	FROM likes
	WHERE user IN(
		SELECT user
			FROM likes
			WHERE item IN(
				SELECT item
					FROM likes
					WHERE user = 'bob'
			)
			AND user != 'bob'
	)
	AND item NOT IN(
		SELECT item
			FROM likes
			WHERE ip = 'bob'
	)
	GROUP BY item
	ORDER BY COUNT(item) DESC

It should retrieve all of the items that 'Bob' likes, searches for other users that like at least one of those items, and then searches for all of the items that those people like (and filters out the things that 'Bob' already likes). Then it sorts the items so that the most popular items are displayed first.

As I said before, I don't normally use a query like this, and certainly not with a subquery within a subquery. Likewise, I'm not sure if this is the best way of doing what I'm trying to achieve. I can see that there are 2 identical subqueries (selecting all items that 'Bob' likes), and was wondering if this query can't be optimized in any way. Removing the subqueries and running a more simple query a few times with different parameters (inside a PHP loop for example) could be a way of doing that, but I'm not sure if that is preferable over 1 'big' query with multiple subqueries.

Well, that's about it. I hope someone is able to provide me with some feedback :)

Thanks,
Lapixx

[debasisdas]

Can you please post your database table structures.

[Lapixx]

I don't have any databases yet, although in this case, I'd imagine creating a very simple table ('likes') with a 'user' and 'item' column, referring to a user ID and an item ID.

[smantscheff]

Set up your table, fill it with some test data and let MySQL EXPLAIN your query. Then try an alternative approach using two left joins from the likes table into itself. See which one is more readable and more efficient (in terms of the EXPLAIN results). Try to guess which query version you are likely to understand after a few month not working on the application.