Retrieving the 25 most recently added entries from all tables in a MySQL database

Question

Hello,

I have a MySQL database (named "sitefeather") which will eventually have several tables with identical structures. The structure is this:

id INT(11) NOT NULL auto_increment, site VARCHAR(1000) NOT NULL, action1 BIGINT(9) NOT NULL, action2 BIGINT(9) NOT NULL, createddatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id), UNIQUE (site)

I would like to print a list of the 25 most recently added entries (called "site" in each table) across all tables. How can I do this with PHP?

Thanks in advance,

John

EDIT: How would I do this for a number of tables that will be variable?

Answer 1

From http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT id FROM table1)
  UNION ALL
(SELECT id FROM table2)
  UNION ALL
(SELECT id FROM table3)
  UNION ALL
(SELECT id FROM tableN)
  ORDER BY createdatetime DESC 
  LIMIT 25

You will really want to look into indexes for optimizations here...

Answer 2

If you're going to be doing this, or querying all tables at once for other reason, very often, you might want to set up a MERGE table that allows easier access to the combined data.

CREATE TABLE merged_table (
    id INT(11) NOT NULL auto_increment,
    site VARCHAR(1000) NOT NULL,
    action1 BIGINT(9) NOT NULL,
    action2 BIGINT(9) NOT NULL,
    createddatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    UNIQUE (site)) ENGINE=MERGE UNION=(table1,table2,table3,tableN) INSERT_METHOD=LAST;

However, this method is only efficient if you're going to be querying the same tables every time.

Answer 3

You will want to do something along the lines of:

(
    select 't' as table_name, id, createdatetime 
    from t 
    order by createdatetime desc 
    limit 25
)
union
(
    select 'u' as table_name, id, createdatetime 
    from u 
    order by createdatetime desc 
    limit 25
)
union
(
    select 'v' as table_name, id, createdatetime 
    from v 
    order by createdatetime desc 
    limit 25
)
order by createdatetime desc
limit 25

This should guarantee you the benefit of any index you have on the createdatetime field of the various tables, and will allow you to determine from which table each of the 25 resulting ids were selected.