Better Performance for getting the Highest Value Row

[David Hadas]

Hi,

I am managing a large table with over 100,000 rows and I need to find a way to retrieve quickly all rows representing highest value on some column in a table while grouped by another column.

For example:
Table tb
>> UID - representing a User Id
>> BID - representing a Book Id
>> LoanTime - representing time that user UID has taken book BID.

The table includes entries representing all books taken by each user and the respective loan time. Example entries are:

UID | BID | LoanTime
1 | 100 | 212
1 | 110 | 343
1 | 322 | 231
3 | 776 | 215
3 | 130 | 212
22 | 120 | 222
43 | 223 | 32

My requested output is:
1 | 110 | 343
3 | 776 | 215
22 | 120 | 222
43 | 223 | 32

The output should include the correct BID value... and it should take a very short time to process (very few seconds I hope).

I was using:

SELECT UID, BID, max(LoanTime) FROM tb GROUP BY LoanTime.

This returns very fast.
Unfortunately, this provides me with a random BID (first one that was found or something like that). I call upon the MySQL guys (doing an excellent job btw,) to think if this can be improved in future versions.

Than I was using:

SELECT * FROM tb tb1 WHERE LoanTime =(SELECT max(LoanTime) FROM tb tb2 WHERE UID = tb1.UID GROUP BY LoanTime)

This returns the correct rows.
Unfortunately, it takes forever!

Can anyone suggest an alternative (possibly by using additional tables) that will be both fast and provide the correct output...

Thanks,
David