# Implementing a query, two types of strategies

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

``````CREATE TABLE groups (
gid int,
iid int,
primary key(gid,iid)
);
CREATE TABLE items (
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"),
(5,"hug"),(6,"art"),(7,"fun");

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

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
WHERE EXISTS
(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.