Preston McMurry wrote:
> It seems to me -- and I may be wrong -- that for each record in the database, you do the subselect
> to see if the record is equal to whatever the max is. Thus, if you have 20,000 records in the
> database, you are looking at 20,000 records for
> each. In other words, 20,000 * 20,000 records.
> That would certainly slow things down. It would be better to do one query to determine the max
> (20,000 hits), then take that value and in a second query look for records which have that as
> the time (20,000 more hits) for a total of 20,000
> + 20,000 (or LOTS less)
I'm afraid your statements are incorrect: there are many maxima: one per name. If the table is properly indexed, you won't need a full table scan to get the maximum for a particular name.
Note to OP: can you also post the result of
explain SELECT ...;
It's also worth comparing (performance-wise) your method with the so-called MAX-CONCAT trick
(See
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html)
--
felix
--
felix