mysql - Efficient way to merge-sort data from multiple tables -

i have series of tables contain data of similar format. i.e. union work. conceptually can think of 1 table partitioned multiple tables. want data of these tables sorted.
problem have data displayed @ once user, need display them in portions i.e. pages.
problem need display data sorted (as said).

so if like:

select table_1   union   select table_2   union   ....   select table_n order col   limit offset, records;   

i doing union , order by e.g. corresponding 50 records of pages on each request.

so how efficiently handle this?

my first attempt union'ing small number of records each table:

( select table_1 order col limit @offset, @records ) union   ...   ( select table_n order col limit @offset, @records ) order col limit @offset, @records 

if above proves insufficient, build 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 method of liking (cron job, triggers on tables table_n, ...). select statement this:

select * ( select * index_table order col limit @offset, @records ) idx left join table_1 on (idx.table_id = 1 , idx.item_id = table_1.id) ... left join table_n on (idx.table_id = n , idx.item_id = table_n.id) 

however, not sure of how such query perform many left join's. depends on how many tables table_n there are.

finally, consider merging tables 1 single table.