我的第一次尝试是UNION’ing只是每个表中的少量记录:
( 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
如果上述证据不足,我会建立一个手动索引表(基于David Starkey的聪明建议).
CREATE TABLE index_table (
table_id INT,
item_id INT,
col DATETIME,
INDEX (col, table_id, id)
);
然后使用您喜欢的方法填充index_table(cron作业,触发表table_n,…).您的SELECT语句将如下所示:
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)
但是,我不确定这样的查询将如何执行如此多的LEFT JOIN.这实际上取决于table_n有多少个表.
最后,我会考虑将所有表合并到一个表中.