MySQL Forums
Forum List  »  Performance

Better Performance for getting the Highest Value Row
Posted by: David Hadas
Date: March 01, 2005 11:35AM


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...


Options: ReplyQuote

Written By
Better Performance for getting the Highest Value Row
March 01, 2005 11:35AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.