Combining two queries with LIMIT into one

November 9, 2022

I query for 50 articles from "publisher1" then for 50 articles from "publisher2". I combine results to return 100 articles.

I tried combining queries to return result simultaneously without success. Combining would be a performance boost since they each take 150ms. How to combine both queries into one or do I have to query separately?

// Query 1
SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher1') ORDER BY datePublished DESC LIMIT 50

// Query 2
SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher2') ORDER BY datePublished DESC LIMIT 50

I tried :

SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher1') ORDER BY datePublished DESC LIMIT 50 AND SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher2') ORDER BY datePublished DESC LIMIT 50

>Solution :

"How to combine both queries into one … ?"

Using UNION :

(SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher1') ORDER BY datePublished DESC LIMIT 50)
UNION
(SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher2') ORDER BY datePublished DESC LIMIT 50);

Or :

(SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher1') LIMIT 50)
UNION
(SELECT * FROM news WHERE (MATCH(snippet) AGAINST('politics' IN BOOLEAN MODE)) AND (publisher = 'publisher2') LIMIT 50)
ORDER BY datePublished DESC;