Mysql – What happens if I load last 10 rows then next 10 after other rows are inserted

limitsMySQLrowselecttimestamp

What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table?

What would happen?

And if the same 10 rows were to be loaded again, and I had a TIMESTAMP column in my table, how would I take that to my vantage to avoid that scenario?

Thanks.

Best Answer

And that is one of 3 problems with using OFFSET for "pagination".

The other two are

What is some rows were deleted?
Performance.

The solution to all of them is to "remember where you left off". Here is my blog on the issues.

Most people who use OFFSET come to this forum bemoaning poor performance. Not many have (yet) discovered the "bug" you are pointing out.

Related Solutions

MySQL: can a table get stuck in the table cache

The table cache does not store data, only MySQL table structs. The query cache cannot return old results because all the queries sing it are invalidated on write, and selects are blocked until that happens (actually causing some contention problems).

I will not discard a MySQL bug, as you are using a 9-year old unsupported version of the server, but you were too quick to discard cache issues/errrors on the other many layers of your application (client, server code, connector, your report code running on the wrong server, etc.).

To solve problems like this, you need to monitor queries sent and results returned at MySQL level and others, so you have enough information to identify the problem. I also recommend you to get familiar with the mysql command line client, as it is the best way to debug server issues.

Mysql – select rows where timestamp is a multiple of n seconds

something like

select * from table where MOD(UNIX_TIMESTAMP(timestamp),300)) = 0;

should do the job. But I am not sure if this is correct. The idea is to try to modulo divide the minute part evenly. Be aware, that this may cause in a full table scan

Best Answer

Related Solutions

MySQL: can a table get stuck in the table cache

Mysql – select rows where timestamp is a multiple of n seconds

Related Question