merging data bt tables in mysql

Question

Hi. I have three tables with the following structure:

tb1: id(AI, PK), uid, date, text
tb2: id(AI, PK), uid, date, text ... and so on

I have to keep them separate because of additional unqiue data that each table has. I'd like to execute a query that will merge and get me the last 20 entries (ie, date DESC) (specifically, i need uid and text) from all 3 tables combined (as if this was one big table).

How can I go about doing this?

Answer 1

SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb1
        ORDER BY
                date DESC
        LIMIT 20
        ) q
UNION ALL
SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb2
        ORDER BY
                date DESC
        LIMIT 20
        ) q2
UNION ALL
        (
        SELECT  uid, text
        FROM    tb3
        ORDER BY
                date DESC
        LIMIT 20
        ) q3
ORDER BY
        date DESC
LIMIT 20

This is more efficient than a mere UNION ALL.

See this article in my blog for performance comparison:

• MySQL: LIMIT on LIMIT