secondary index

[Anon]

Hi.

I've built a little site with php/mysql to server as a bulletin board for some friends. My table of messages looks like this:

posts:
msg_id varchar(100),
user_id INT,
day DATETIME,
primary key(msg_id)

What I would like to do is this: given a particular msg_id, find the 'next' msg_id where by 'next' I mean the message posted immediately after it.

So I guess I want something that acts like a secondary index based on date.

Any idea? And thanks much,
Matt

[Anon]

Ah, I think I have a decent solution:

first, find the date of the current message.

then do something like this:

select * from posts where day > CURRENT_MSG_DATETIME LIMIT 1

Matt

[kparker]

And if two posts have the same timestamp?

[smarlowe]

you need to add an order by there or you'll just get any old post posted after the one selected.

select * from posts where timestamp > $ourstamp order by timestamp limit 1

should do it. If you wanna make sure two messages don't have the same time stamp, use custom made ones from microtime, and / or make a unique index on that column.

And yes, it will help in most databases to have an index on the timestamp field.

[Anon]

I guess I'll be hosed in that case...! My site doesn't get too much traffic so I'll just ignore this.

but a valid point. thanks for bringing it to my attention.

matt