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.
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.
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.
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
Reply With Quote
Originally Posted by yamaharuss
Bookmarks