# MySQL limit across multiple tables in multiple queries

I have a website that keeps track of 'changes' that users make. These changes come in all shapes and sizes and each shape and size has a different MySQL table to store them in. In total there are 8 different tables. Each table has completely different columns, so I don't think a simple JOIN would be possible, however they all have a time column.

What I would like to do is show the last x number of changes made across all tables (i.e. sorted by time). How I am doing it now is querying all tables like this:

foreach ($tables as$table) {
$stmt =$this->_dbh->prepare("SELECT $table.*, realname, time, reservations.conf_num, reservations.conf_letters FROM$table
LEFT JOIN users ON $table.user_id = users.id LEFT JOIN reservations ON$table.conf_num = reservations.conf_num
ORDER BY
time DESC
LIMIT ".$this->_num_values);$stmt->execute();

$changes[$table] = $stmt->fetchAll(); }  Then I sort and slice the resultant array: ksort($changes);
$changes = array_reverse($changes);

// Make sure to limit it if we have a maximum number of values
if ($this->_num_values > 0) {$changes = array_slice($changes, 0,$this->_num_values);
}


This works fine, however it just seems extremely inefficient to me since we are retrieving the latest x values from all tables instead of just returning the exact number so that the sum of the number of rows from the individual queries equals x. Is there a better way to do this, or am I just trying to micro-optimize too much here?

• Edit comment. I'm working on it. I'll keep you posted if I have any trouble working this out. I've never done UNION before. – Mike Mar 19 '13 at 19:37