Efficient way to merge-sort data from multiple tables

Question

I have a series of tables that contain data of similar format. I.e. a UNION would work. Conceptually you can think of it as 1 table partitioned into multiple tables. I want to get the data from all of these tables sorted.
Now the problem I have is that the data are too much to be displayed all at once to the user, so I need to display them in portions i.e. pages.
Now my problem is that I need to display the data sorted (as already said).

So if I do something like:

SELECT FROM TABLE_1  
UNION  
SELECT FROM TABLE_2  
UNION  
....  
SELECT FROM TABLE_N
ORDER BY COL  
LIMIT OFFSET, RECORDS;  

I would constantly be doing a UNION and ORDER BY to get e.g. the just the corresponding 50 records of the pages on each request.

So how would I most efficiently handle this?

Answer 1

My first attempt would be UNION'ing just a small number of records from each table:

( SELECT FROM table_1 ORDER BY col LIMIT @offset, @records )
UNION  
...  
( SELECT FROM table_N ORDER BY col LIMIT @offset, @records )
ORDER BY col LIMIT @offset, @records

If the above proves insufficient, I would build a manual index table (based on David Starkey's clever suggestion).

CREATE TABLE index_table (
    table_id INT,
    item_id INT,
    col DATETIME,
    INDEX (col, table_id, id)
);

Then populate index_table with a method of your liking (cron job, triggers on tables table_n, ...). Your SELECT statement would then look like this:

SELECT *
FROM ( SELECT * FROM index_table ORDER BY col LIMIT @offset, @records ) AS idx
LEFT JOIN table_1 ON (idx.table_id = 1 AND idx.item_id = table_1.id)
...
LEFT JOIN table_n ON (idx.table_id = n AND idx.item_id = table_n.id)

However, I am not sure of how such a query would perform with so many LEFT JOIN's. It really depends on how many tables table_n there are.

Finally, I would consider merging all tables into one single table.