开发者

Is using a unique datetime or timestamp for database records a bad idea?

I'm fairly new to the SQL world and am designing a database that will record real-time data at up to 32Hz. It seems logical to use the timestamp as part of the primary key, or at least to make it unique (and perhaps composite with other information). My question is about performance as the size of the table increases. Naively, I'm thinking that if the database must check the timestamp is unique everytime I insert a record, things will get really slow after a while. But then again this is probably something database optimize开发者_JAVA技巧rs solved years ago and it's perfectly efficient to define timestamp as unique on large scale databases.

Note I'm using MySQL and timestamp for my database. Also I don't actually need timestamp to be unique, it just makes me feel better knowing the schema is as tightly defined as possible.


"I don't actually need timestamp to be unique". Then I suggest you work out what the real business requirements are and determine appropriate keys from that. A solid database design is one which accurately models the business domain, implements the rules of that domain and meets the users other requirements. There is no single answer to your question for every possible requirement.


MySql's date/time types can not store fractions of a second. You would have to store the fraction in an extra column along side the datetime or timestamp column. The fraction would have to be supplied by the application.

Making the timestamp unique in a very large table would sure make the insertion slower. If it is real time capturing then it could make it nonviable at 32Hz.


I've found it to be a good idea to let the primary key be a auto_increment field and nothing else. That way you know you have a primary key that is easily handleable by whatever you want to use to read this data.

Then it might be a very good idea to have a timestamp as well, especially if the data isn't 100% regular in 32Hz. How will you otherwise know when the data was added?


It all depends on how many records you plan on storing? An hour, a day...? Though, a better (faster, but more space consumpting) solution would be to have a primary key autoincrementing and store the timestamp for your reference, but if your data is sequential (and I assume it is), even timestamping is not neccessary, autoincremented field will be your sequence denominator.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜