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?
