Archive for May 8th, 2010
Answering questions asked on the site.
Ivo Radev asks:
I am trying to make a very simple query.
We have a log table which different machines write to. Given the machine list, I need to find the latest log timestamp.
Currently, the query looks like this:SELECT MAX(log_time) FROM log_table WHERE log_machine IN ($machines)
, and I pass the comma-separated list of
The weird thing is that the query is literally instant when there is only one machine (any) in the list but slow when there are multiple machines.
I'm considering doing it in separate queries and then process the results in PHP. However I'd like to know if there is a fast solution in MySQL.
Most probably, there is a composite index on
(log_machine, log_time) which is being used for the query.
Usually, a query like this:
SELECT MAX(log_time) FROM log_table
on the indexed field
log_time can be served with a single index seek on the index.
MAX(log_time), by definition, is the latest entry in the index order, and can be fetched merely by finding the trailing index entry. It's a matter of several page reads in the
B-Tree, each one following the rightmost link to the lower-level page.
Similarly, this query:
SELECT MAX(log_time) FROM log_table WHERE log_machine = $my_machine
can be served with a single index seek too. However, the index should include
log_machine as a leading column.
In this case, a set of records satisfying the
WHERE clause of the query is represented by a single logically continuous block of records in the index, each one sharing the same value of
MAX(log_time) will of course be held by the last record in this block. MySQL just finds that last record and takes the
log_time out of it.
Now, what if we have a multiple condition on
Read the rest of this entry »