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
(BTW, UID and BID together are unique - if it helps anyone)

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 to think if this can be improved in future versions.

Then 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

what indexes are defined on the table?

In my implementation, the table tb is a memory table built from another table (I can use a temporary table just the same). So any combinations of keys can be used to best perform a fast data extraction.

What would you suggest?

David

why not just run your query against the table that you built tb from instead, instead of building tb?