Efficient way to merge-sort data from multiple tables
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?
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.