TimeStamp as Key?

Good or Bad?

[Nick_W]

Hi all,

if I need to order entries in MySQL by a timestamp column should I make it an INDEX?

Is it wise to use a timestamp as an index or is the fact that they are timestamps good enough for sorting the table without worrying?

Many thanks...

Nick

[ukgimp]

Nick

If you have an autoincrement as your PK then could you not order by that?

I would be worried that two timestamps could be exactly the same. It is remotely possible.

Cheers

[Nick_W]

Sorry, edited my post ;)

I can make the timestamp just an INDEX of course which will stop the 'duplicate' problem.

No, no auto_increment on this table, it's not appropriate in this situation.

I need to order by date entered and the primary key

id INT NOT BULL,
auth INT NOT NULL,
date TIMESTAMP NOT NULL,
text TEXT NOT NULL
PRIMARY KEY(id)

is what I have now...

Nick

[jatar_k]

What will happen when you update a row? The timestamp will change. I would make it a date field and then use NOW() to insert the date.

[jatar_k]

[mysql.com...]

A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself.

The way to have a timestamp for created/inserted is to have two cols. The first one is created and the second one is last modified. If I remember correctly mysql only updates the second timestamp column on update.

[Nick_W]

No, I think timestamp is correct. These are comments on a blog entry so they need to be ordered by the time they were inserted and not changed on update.....

Nick

[Nick_W]

Ooops! - just re-read your post...

Well, the way I've been doing it till now with updates where the timestamp should not change is to do tstampfield=tstampfield in the query.

Works just fine but possiblly not as elegant as yours ;)

Nick

[jatar_k]

as long as you are accounting for the timestamp type behaviour then you are alright and as long as you using the largest timestamp you can the possibilty for dups is very small.

[Nick_W]

Yeah, just re-reading some stuff on date/time colunns and I still prefer the timestamp for this case...

So, what do you think about making it an index in the above situation Jatar?

Is it over kill? - I'd typically have to get all entries with id=n order by date

Unless you tell me something I've not thought of I think i'll actually leave it, I can always add one and benchmark it later I guess...

Thanks!

Nick

[lorax]

Indexes in MySQL [mysql.com] are primarily used for data retrieval. If the index is not part of the query criteria then it really serves no purpose. But if it will be used as criteria for a query then it will make a difference. Especially if you need to do things like select info in a range of dates (from now to 30 days ago) then you can use MySQL to describe the query and it will return the data snappy quick.

[Nick_W]

so they need to be ordered by the time they were inserted

Same thing right?

SELECT blah,blah,date from blah ORDER BY date

Nick

[jatar_k]

yep, you can probably leave it, sounds like it will do just fine.

[DrDoc]

If I remember correctly mysql only updates the second timestamp column on update.

I believe it's only the first timestamp that will be updated ;)

http://www.mysql.com/doc/en/DATETIME.html
If you have multiple TIMESTAMP columns, only the first one is updated automatically.

And Nick, "NOT BULL"? ...is that a way of filtering junk from the database? ;)