Tables have not been created yet, but to simplify there is a groups and an items tables. A group contains an item id (and a group id). And several groups may contain the same item id.

For instance

          gid int,
          iid int,
          primary key(gid,iid)
          iid int primary key,
          label char(5)

and let put some data in these, to help visualize the coming problem

insert into items values (1,"pen"),(2,"gum"),(3,"cat"),(4,"dog"),

insert into groups values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,1),

Retrieving items of multiple groups (e.g. groups 1,2,3,4) uses a DISTINCT to remove duplicates

(1) SELECT DISTINCT i.iid,i.label FROM groups g JOIN items i ON g.iid=i.iid 
    WHERE g.gid in (1,2,3,4) ORDER BY i.iid;

But since the tables will eventually contain more data, would that query using a subquery be more efficient

(2) SELECT i.iid,i.label FROM items i 
    JOIN (SELECT DISTINCT iid FROM groups WHERE gid IN (1,2,3,4)) AS s
    ON i.iid=s.iid ORDER BY i.iid;

since distinct applies only to the items ids, despite the overhead of using a subquery?

Or this one suggested by @hypercube (see comments):

(3) SELECT i.iid,i.label FROM items AS i 
       (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid)
    ORDER BY i.iid

For information, eventually

  • the items table will have between 50~100k rows
  • a group will likely have 5~10k items
  • groups ids to be selected in a single query are in the 5~20 range
  • probability that an item is part of 2 selected groups: 50%
  • probability that an item is part of 3 selected groups: 30%
  • probability that an item is part of 4 selected groups: 10%
  • Why not populate the tables with sample data (up to the 100K rows you expect) and test both queries for performance? And add an index on (iid, gid) as well before testing. Some queries may prefer that index. – ypercubeᵀᴹ Oct 29 '17 at 9:08
  • You can also test this one: SELECT i.iid,i.label FROM items AS i WHERE EXISTS (SELECT * FROM groups AS g WHERE g.gid IN (1,2,3,4) AND i.iid=g.iid) ORDER BY i.iid; – ypercubeᵀᴹ Oct 29 '17 at 9:10
  • How many items do you expect to have in a group? There will be performance differences depending on that number: i.e. if some group has only 5 or 10K items, that will affect - differently - the efficiency of each query. – ypercubeᵀᴹ Oct 29 '17 at 9:13
  • Question edited (3rd query, and number of items in a group). Thanks for the suggestions, but the whole point of the question was to avoid having to perform multiple tests, and rely on this site people experience ; the queries being relatively simple (and sometimes the tests only show speed performance, while under the hood the server might allocate much more memory for a given query - memory is just an example, there could be other reasons). – Ring Ø Oct 29 '17 at 9:41

(1) is simple and straightforward. It may be the most efficient.

(2) is a common speed-up. But it shines when the non-indexed part (just i.label in your case) is bulky and/or the key into the table (i.id) is not the PRIMARY KEY. So, if your example is watered down from the real code, (2) may outperform (1).

(3) is unlikely to be efficient, since it needs to reach into items 50~100K times.

Side issues:

Don't use CHAR unless the column really is fixed length; instead, use VARCHAR.

I assume you are using InnoDB. (MyISAM, because of different handling of PRIMARY KEY, will be less efficient.)

If groups is a many:many mapping table, see my tips. It will probably help (3) some.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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