1

I'm trying to optimize a query that returns the membership of objects in two groups. Group "a" is a one-to-one link table (ie. object_id is a unique foreign key in group_a) and group "b" is one-to-many (object_id can have multiple entries in group_b). Here is a simplified version of the query:

-- show objects and their membership w/ LEFT JOIN
SELECT DISTINCT o.name,
    a.object_id IS NOT NULL as in_group_a,
    b.object_id IS NOT NULL as in_group_b
FROM object o
LEFT JOIN group_a a ON (a.object_id = o.object_id)
LEFT JOIN group_b b ON (b.object_id = o.object_id)
WHERE a.object_id IS NOT NULL
OR b.object_id IS NOT NULL;

It returns something like this:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

Note that Barney is in both groups but only one row is returned; this is critical. I tried to rewrite this query with a union:

-- show objects and their membership w/ UNION
SELECT o.name, tmp.in_group_a, tmp.in_group_b
FROM ((
    SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
    FROM group_a a
) UNION (
    SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
    FROM group_b b
)) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id);

This query is 10 times faster but I get multiple rows for objects in both groups:

+--------+------------+------------+
| name   | in_group_a | in_group_b |
+--------+------------+------------+
| Fred   |          1 |          0 |
| Barney |          1 |          0 |
| Barney |          0 |          1 |
| Betty  |          0 |          1 |
+--------+------------+------------+

I'm guessing the original query is slow because it starts with the object table, which is large and most entries are not in either group.

Is it possible to make the union query return the results I want, and if not is there another way to optimize this query?

FYI, here are my simplified example tables and data:

CREATE TABLE object (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(64) NOT NULL,
    PRIMARY KEY (object_id)
);

CREATE TABLE group_a (
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

CREATE TABLE group_b (
    group_b_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    object_id INTEGER UNSIGNED NOT NULL DEFAULT 0,
    description VARCHAR(64) NOT NULL,
    UNIQUE KEY (group_b_id),
    KEY (object_id),
    FOREIGN KEY (object_id) REFERENCES object (object_id)
);

INSERT INTO object VALUES (1, "Fred"), (2, "Barney"), (3, "Wilma"), (4, "Betty");
INSERT INTO group_a VALUES (1), (2);
INSERT INTO group_b VALUES (1, 2, "a"), (2, 4, "a"), (3, 4, "b"), (4, 4, "c");

Thanks!

3
  • SELECT o.name, sum(tmp.in_group_a), sum(tmp.in_group_b) .... group by o.name do this in your second query. Mar 30, 2017 at 22:18
  • Hah! That was too simple to claim an answer eh? Not sure why that didn't occur to me... Anyway thank you, I will accept your answer as repeated by @Jason.
    – Kruthers
    Mar 31, 2017 at 0:07
  • No needed I was just passing through here and saw your question and decided to help without an answer... I spend most of my time on stackoverflow rather than here :). Glad that I could help. Mar 31, 2017 at 0:28

2 Answers 2

0

@Jorge Campos is correct

SELECT o.name, sum(tmp.in_group_a), sum(tmp.in_group_b)
FROM ((
   SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
   FROM group_a a
   ) UNION (
   SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
   FROM group_b b
   )) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id)
GROUP BY o.name;
0

This might be simpler and faster:

SELECT  name,
        EXISTS ( SELECT 1 FROM group_a WHERE object_id = o.object_id )
                         AS in_group_a,
        EXISTS ( SELECT 1 FROM group_b WHERE object_id = o.object_id )
                         AS in_group_b
    FROM object AS o

If necessary, tack on

    HAVING in_group_a
        OR in_group_b
3
  • Didn't know about EXISTS; I like that, it's more clear. However performance is interesting with my real data... With a small LIMIT this is much faster, but as I page through the results with larger offsets, it quickly gets slower than the UNION query. The UNION query has consistent timing regardless of the limit or offset. Very odd...
    – Kruthers
    Apr 3, 2017 at 19:28
  • No need for OFFSET or LIMIT with EXISTS. And your original question did not mention pagination. What are you talking about?
    – Rick James
    Apr 3, 2017 at 22:14
  • Look at the example result with 3 rows (in the original question); this is just a toy version of my real dataset. In reality the query results are many thousands of rows long and thus I use LIMIT/OFFSET to fetch a page at a time. But that doesn't matter; returning all rows of my real data is much faster with the UNION than with the EXISTS method.
    – Kruthers
    Apr 6, 2017 at 14:08

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.