Create a Single Subquery from Two Queries without UNION

Viewed 30

I am trying to create a subquery (DB Fiddle) to integrate into the main query.

There are two queries used as references to create the subquery.

The subquery requires producing the same output as creating a UNION of these two queries.

Query I

SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN engages eg1 ON eg1.prid = bk1.prid
WHERE eg1.id = 2 AND eg1.typ = 1;

| id  |
| --- |
| 1   |
| 2   |
| 3   |

---

Query II

SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN promotes pm1 ON pm1.bkid = bk1.id
INNER JOIN engages eg1 ON eg1.prid = pm1.prid
WHERE eg1.id = 2 AND eg1.typ = 1
AND pm1.point = 5 AND bk1.prid <> 2;

| id  |
| --- |
| 5   |

What I have tried so far:

books: id, prid, title, ...
engages: id, prid, typ, ...
promotes: bkid, prid, point, ...

---

SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN engages eg1 ON eg1.prid = bk1.prid
INNER JOIN promotes pm1 ON pm1.point = 5
WHERE eg1.id = 2 AND eg1.typ = 1
OR (pm1.bkid = bk1.id AND bk1.prid <> 2);

| id  |
| --- |
| 1   |
| 2   |
| 3   |

---

Desired output:

| id  |
| --- |
| 1   |
| 2   |
| 3   |
| 5   |

---

Main Query:

SELECT bk.*, ab.col1, ab.col2, bc.col2, SUM (xyz), ...
FROM books bk 
INNER JOIN ab_table ab ...
LEFT JOIN bc_table bc...
<OTHER JOINS>
INNER JOIN (SUBQUERY) sq ON sq.id = bk.id
WHERE <OTHER CONDITIONS>
GROUP BY bk.id ORDER BY bk.id DESC
LIMIT 50

Minimum reproducible code:

CREATE TABLE `books` (
  `id` int(10) UNSIGNED NOT NULL,
  `prid` int(10) UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL
);

INSERT INTO `books` (`id`, `prid`, `title`) VALUES
(1, 1, 'Physics'),
(2, 1, 'Chemistry'),
(3, 1, 'Maths'),
(4, 2, 'Zoology'),
(5, 3, 'Biology');

CREATE TABLE `engages` (
  `id` int(11) NOT NULL,
  `prid` int(11) NOT NULL,
  `typ` bit(1) NOT NULL DEFAULT b'1'
);

INSERT INTO `engages` (`id`, `prid`, `typ`) VALUES
(2, 1, b'1');

CREATE TABLE `promotes` (
  `bkid` int(11) NOT NULL,
  `prid` int(11) NOT NULL,
  `point` tinyint(1) NOT NULL DEFAULT 5,
  `enroll` bit(1) NOT NULL DEFAULT b'1'
);

INSERT INTO `promotes` (`bkid`, `prid`, `point`, `enroll`) VALUES
(1, 1, 0, b'1'),
(2, 1, 0, b'0'),
(3, 1, 0, b'1'),
(4, 1, 5, b'1'),
(4, 2, 0, b'1'),
(5, 1, 5, b'1'),
(5, 3, 0, b'0');

ALTER TABLE `books` ADD PRIMARY KEY (`id`);
  
ALTER TABLE `engages` ADD PRIMARY KEY (`id`,`prid`);
  
ALTER TABLE `promotes` ADD PRIMARY KEY (`bkid`,`prid`);
  
ALTER TABLE `books` MODIFY `id` int(10)
UNSIGNED NOT NULL AUTO_INCREMENT;
  
ALTER TABLE `books` ADD INDEX(`prid`);

ALTER TABLE `engages` ADD INDEX(`id`, `prid`);

ALTER TABLE `promotes` ADD INDEX(`prid`, `bkid`);

The books table has millions of rows.

The main query runs in chunks and executes in a fair minimal time.

What should be the correct (and performance-optimum) subquery to produce the desired output without the UNION of those two queries?

Update

prid is a foreign key that refers to the primary key of a table named person. The same is true for the id column in the engages table.

1 Answers
  • Lack of indexes seems to be the main problem.
  • OR is usually terrible for performance; UNION is often the solution (not the problem).

Books probably needs

PRIMARY KEY(id)
  • Is promotes a many-to-many mapping table? If so:

    PRIMARY KEY(bkid, pride)
    INDEX(prid, bkid)
    

and get rid of prid from books. If it is not many-to-many, please describe the relationship (eg, 1:many)

  • If engages needs PRIMARY KEY(id), why also test the type? (WHERE eg1.id = 2 AND eg1.typ = 1)

Get those taken care of, then I will check again.

Related