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 :)