www.webdeveloper.com
Results 1 to 4 of 4

Thread: grabbing most recent record from table

  1. #1
    Join Date
    Mar 2011
    Posts
    103

    grabbing most recent record from table

    Hi. I have a mysql table, let's call it log, that I expect may become very large someday. The table has fields like this (well, these are the important ones):

    id1,id2,id3,timestamp

    I do lot's of selects like this

    select * from log where id1=? and id2=? and id3=? order by timestamp desc limit 1

    Now, I'm guessing that the order by clause might get expensive if the table is very large (is this right?). And new records get inserted very often (does this mean that using an index on id1,id2 and id3 is a bad idea? that's what I read anyway).

    What I'm wondering is, are newly inserted records ALWAYS appended to the "end" of the table? If so, can I remove the order by clause and somehow tell mysql to start searching from the end of the table and stop when it finds the first match? Will this increase performance (i.e. time it takes to do the select)? Will it reliably fetch the most recently inserted record where id1=? and id2=? and id3=? ?

    I hope I'm being clear in what I mean. Let me know if I need to clarify. Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Rosemount, MN
    Posts
    2,287
    I do orders on tables with 500 million rows, and it runs in under 2 seconds. It just depends on how good your indexes are.

    Mysql doesn't always put the new record at the end of the file, if something gets deleted mysql will more than likely fill that spot with the new record.
    My settings

    Browser :: FireFox 1.5
    Resolution :: 1152x864
    Connection :: Cable Modem 2Mbs

  3. #3
    Join Date
    Jan 2008
    Location
    Florida
    Posts
    1,227
    The order by clause won't have much affect on performance but you should replace your * with the actual fields you are using. That would be your biggest boost.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,157
    Quote Originally Posted by yamaharuss View Post
    The order by clause won't have much affect on performance but you should replace your * with the actual fields you are using. That would be your biggest boost.
    This assumes that, as previously mentioned, your table is indexed accordingly (e.g. an index on the timestamp column, in this case)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles