I have three tables: items, members, subscriptions. One item can be get by 1 or more members (one member can get more items), so subscriptions represents the many-to-many relationship between items and members. So we have:

  • items(id, name)
  • members(id, name, email)
  • subscriptions(*id_item*, *id_member*)

And now my problem: I have a quite comlicated query which manage items and outputs items data, I want to add to this output a column that contains the number of members who subscribed that item. I wrote this query but I don't want to use sub select statement

(SELECT count(*) AS current_sub from subscriptions AS s where s.id_item = i.id) AS current_members,
  -- ..... other code....
FROM items AS i
// ....other code...

I use MySQL 5.5. Thanks in advance.

1 Answer 1


This one might work better:

items AS i
INNER JOIN (SELECT id_item, COUNT(*) AS count_subs FROM subscriptions GROUP BY id_item) counts
      ON i.id = counts.id_item

Post the result of the query with EXPLAIN keyword in front of SELECT. Then we can see, if and how indexes are used.

  • Thanks, it works. Regarding EXPLAIN, here there's the row of subscriptions table (table,type,possible_keys,key,key_len,ref,rows,Extra): subscriptions|index|NULL|id_item|6|NULL|5|Using index. Dec 31, 2013 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.