As i know MS SQL keeps the date in float so making an index on such field is not a problem, i would like to know if the same physical representation of datetime is applied in MySQL case.
Can you please provide me some details about this? I tried to find the information in MySQL doc but it was impossible.
Is ok to use datetime as primary key in MySQL?
Moderator: General Moderators
-
BornForCode
- Forum Contributor
- Posts: 147
- Joined: Mon Feb 11, 2008 1:56 am
Re: Is ok to use datetime as primary key in MySQL?
Not something I'd do. MySQL stores a datetime as two sets of 4 byte integers in a packed format ... years*10000 + months*100 + days and hours*10000 + minutes*100 + seconds. If you were certain you'd never need to store two records with the same key then it would work, but in my experience no matter how certain you are now whatever it is will throw up a problem in the future.
Re: Is ok to use datetime as primary key in MySQL?
Agreed, the PK should really be as unique as humanly possible - and with a field thats also used as a variable, you could easily get messed up. I'd stick with an auto incrementing standard integer.
Re: Is ok to use datetime as primary key in MySQL?
It's OK to use if you are sure you want to have the restriction of only having at most one entry per second. If the time needs to be unique, making it the primary key is alright. If, though, you just want a unique key per row, do as ~Inkyskin says & make it an auto incrementing integer column.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
-
BornForCode
- Forum Contributor
- Posts: 147
- Joined: Mon Feb 11, 2008 1:56 am
Re: Is ok to use datetime as primary key in MySQL?
The table is an aggregation one and i won't be concurrent inserts or so on, the finest value that i need is up to hour (not minutes and secs).
If i make a pk using an auto_increment i will have to create unique on the date column to and this is somehow redundant
If i make a pk using an auto_increment i will have to create unique on the date column to and this is somehow redundant
Re: Is ok to use datetime as primary key in MySQL?
Redundancy can be a good thing. Your currently relying on best practice or a cron job to ensure your data makes sense. Why not put a hard rule in the database (the primary key) to ensure data integrity? If you're only writing once an hour, there won't be any serious write delays due to indexing, and your reads will be a bit faster.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.