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