MySQL limit across multiple tables in multiple queries

Question

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?

Answer 1

Yes, there is: create a stored procedure and UNION the result sets then sort the records and return them to PHP.

• Mike: 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: It was complicated and took me 2-3 hours, but I finally have it working. Thanks for the suggestion.