On 4/18/07, Tim Lucia <[EMAIL PROTECTED]> wrote:
-----Original Message----- From: John Comerford [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 10:50 PM To: mysql@lists.mysql.com Subject: Millisecond time stamp Hi Folks, I am putting together a table to hold log entries. I was going to index it on a field with a type of TimeStamp. Unfortunately this will not suit my needs because I could have more than one log entry per second. As far as my (limited) knowledge goes I have two options to get around this: A) One character string contain a string I build with the date and milliseconds tagged onto the end b) Two separate fields Field1 TimeStamp Field2 Milliseconds
WHY are you indexing the Timestamp? It should not be your primary key because, as you point out, it might allow duplicates. Even case B is not foolproof. If you're indexing it for searching, then your application should be prepared to handle multiple log records for any value. For uniqueness, add an auto_increment primary key column. That way, you can tell two+ log records apart even when they have the same timestamp. Tim ----- Dave Ashley's note: Tim's solution is better than the one I proposed if this is your only problem. In my applications, typically the need for unique identifiers comes up in may places, so I'm used to using that style of solution. However, adding an auto-increment primary key column will get the same effect with a lot less work. Also, it is probably more efficient due to the absence of a spin-lock.
