Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    2

    Unanswered: Better Performance for getting the Highest Value Row

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what indexes are defined on the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why not just run your query against the table that you built tb from instead, instead of building tb?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •