Retrieve data in chunks of rows from a frequently updated mysql table

Question

My table is of the form below

| ID | TIME                 | USER | DATA|

| 1 | 1542785267683 | A         | hi      |

| 2 | 1542785385809 | B         | hello |

| 3 | 1542785385809 | C         | hey   |

| 4 | 1542785385809 | D         | hii     |

| 5 | 1542785385809 | E         | hola  |

| 6 | 1542795485058 | F         | hiiiii   |

The table will be in same form as above with 1000s of rows. I want to retrieve the latest 3 rows and then the next 3 rows from the table to display the rows to the user, same as you view messages when you swipe upwards in messaging apps.

I tried the following methods:

1. Retrieval based on time of the last message of first selected chunk. But it is not possible since two or more rows could be in same time and this will result in displaying the same row twice.

SELECT * FROM MessageM WHERE TIME_RECEIVED < NOW() ORDER BY TIME_RECEIVED DESC LIMIT 3;

SELECT * FROM MessageM WHERE TIME_RECEIVED < 1542785385809 ORDER BY TIME_RECEIVED DESC LIMIT 3; # Leaves two row

SELECT * FROM MessageM WHERE TIME_RECEIVED <= 1542785385809 ORDER BY TIME_RECEIVED DESC LIMIT 3; # duplicates two row

2. I read the answer mentioned below

MySQL : retrieve a large select by chunks

My requirement is almost the same except the table could be updated in between the time to chunks were retrieved.

So When using the following queries

SELECT * FROM Message ORDER BY TIME DESC LIMIT 0, 2;

SELECT * FROM Message ORDER BY TIME DESC LIMIT 2, 4;

If a new row is inserted between the consecutive selects the same row already displayed will again be obtained for the next select statement. Locking is not at all possible.

Could auto increment column solve the problem or Any other methods like adding new column with some other constraint solve the problem or some form of partition.

I have planned on using the following query

SELECT * FROM Message WHERE TIME <= 1542785385809 AND ID NOT IN (5, 4) ORDER BY TIME DESC LIMIT 3;

Get the time of the last row and get the ids of all rows with the given time from the retrieved rows. When querying for the second time enter the ids in NOT IN condition.

Is there any other better way to do it. Could auto increment column solve the problem or Any other methods like adding new column with some other constraint or some form of partition.

Answer 1

First, do not use time column to sort - use ID instead. I suppose your ID column is an auto-increment integer, so it's unique and time-of-insert sorted.

Second, let's consider an approach used by social networks (for example LinkedIn): you load the feed (latest N rows). When you scroll to the bottom of the page next rows are loaded (another N rows). When new posts become available, you push "New posts" button which resets current view completely and goes to the beginning. This takes you back to step 1, efficiently loading latest N rows.

Technically, this is done this way:

SELECT * FROM MessageM ORDER BY ID DESC LIMIT 3;

Returning:

| 6 | 1542795485058 | F | hiiiii |

| 5 | 1542785385809 | E | hola |

| 4 | 1542785385809 | D | hii |

You remember ID=4 as the last seen post. For the next page you load:

SELECT * FROM MessageM WHERE ID < 4 ORDER BY ID DESC LIMIT 3;

Returning:

| 3 | 1542785385809 | C | hey |

| 2 | 1542785385809 | B | hello |

| 1 | 1542785267683 | A | hi |

You remember ID=1 as the last seen post. For the next page you load:

SELECT * FROM MessageM WHERE ID < 1 ORDER BY ID DESC LIMIT 3;

Returning no rows. This means you can not load any older posts.

To reset everything you just go to step 1:

SELECT * FROM MessageM ORDER BY ID DESC LIMIT 3;

Returning:

| 7 | 1542785385809 | A | some new post |

| 6 | 1542795485058 | F | hiiiii |

| 5 | 1542785385809 | E | hola |

So you read the new post and you also have more pages now.