MySQL偏移/按日期/限制排序

I'm having a kind of timeline such as in twitter/facebook, I have a main view, and I need to get the latest updates (timer), and be able to get previous updates (scrolling down). I limit the main view to 25 updates.

I have these columns (more but not relevant)

col_id - auto increment
col_date - timestamp
col_content the content

Basically with queries like these:

SELECT *
FROM table
WHERE col_id < $col_id
ORDER BY com_id DESC
LIMIT 0, 25

Currently, I'm sorting by col_id, and this way I can send the highest col_id used to get the latest updates (e.g. WHERE col_id > $col_id), and I have the smallest col_id to get previous updates (WHERE col_id < $col_id)

But I need everything to be sorted by date. But then I can't use the col_id to gather new/previous updates. So I could use the date, however if there are multiple columns with the exact same timestamp, and the 25 limit cuts it somewhere in the middle, I can't use WHERE col_date > $col_date, because I would miss updates.

I could use WHERE col_date >= $col_date, but then I need to ignore the dupes from the new ones with the current ones. I can also imagine a situation where there are over 25 updates with the same timestamp, and then I would be getting nowhere.

Is there some way I can combine the date and col_id? Or maybe a new column? I don't know how to tackle this issue.

Thanks!

You should record both latest col_date and col_id, and use them like this:

SELECT  *
FROM    mytable
WHERE   (col_date, col_id) < ($latest_col_date, $latest_col_id)
ORDER BY
        col_date DESC, col_id DESC
LIMIT 25

Create a composite index on (col_date, col_id) for this to work fast.

The ORDER BY clause accepts multiple arguments

try this:

ORDER BY col_data DESC,col_id DESC

This will sort by date and then by id.

In addition to that you could also get 100 records and then just filter the ones till you get your result.

Yes, you can order by two columns.

SELECT *
FROM table
WHERE col_id < $col_id
ORDER BY col_date DESC, com_id DESC
LIMIT 0, 25

This question has already been answered here. Have a look - PHP MySQL Order by Two Columns

Why can't you use col_id for the issue?

One possibility is to use another column - the same way they do on reddit. If you go on first page, and click next, you see the url: ?count=25&after=t3_1mr2nk.

SELECT *
FROM table
WHERE col_date <= $col_date AND (col_date < $col_date OR col_id < $col_id)
ORDER BY cold_date DESC, col_id DESC
LIMIT 0, 25