myBatis offset position setting issue for infinite scroll

Question

I am building a Quora like application. The backend uses spring boot, mybatis to connect to a mysql database. When a user opens the website, the backend returns the first 10 questions. If the user clicks the "fetch more" button, the backend should return the next 10 questions.

The mybatis code is

<mapper namespace="com.quora.dao.QuestionDAO">
    <sql id="table">question</sql>
    <sql id="selectFields">id, title, content, comment_count,created_date,user_id
    </sql>
    <select id="selectLatestQuestions" resultType="com.quora.model.Question">
        SELECT
        <include refid="selectFields"/>
        FROM
        <include refid="table"/>

        <if test="userId != 0">
            WHERE user_id = #{userId}
        </if>
        ORDER BY id DESC
        LIMIT #{offset},#{limit}
    </select>
</mapper>

Currently, my logic is that the first time #{offset} is 0, the second time #{offset} is 10. But I find that this logic is not correct when the table updates frequently. The user may get duplicate data if the table has been inserted new rows. How can I set the #{offset} based on the last question id shown in the front end? For example, the last question id in the front end is 10, then the #{offset} should be the row number of question id 10.

Can anyone give me some suggestions?

Thanks, Peter

Answer 1

The general idea is to not use OFFSET at all and do filtering instead. If you can define the order on the messages so that it does not change when new messages are inserted (for example you incrementally generate ids and sort message by id ASC) then it is easy:

SELECT id, some_other_field, yet_another_field
FROM question
<if test="last_seen_question_id != null">
    WHERE id > #{last_seen_question_id}
</if>
ORDER BY id ASC
LIMIT #{limit}

Then client should use the last seen question id and pass it when it wants to fetch the next page.

From your query (ORDER BY id DESC) it looks like you want to see the newest questions in the top. This is a bit of a problem as newly inserted questions will tend to get to the top.

If you are ok to get new questions on the next page first and then older questions you can do like this:

<!-- This condition is needed to avoid duplication when the first page is fetched
     and we haven't seen any question yet.
     In this case we just get up to limit of the last questions.
-->
<if test="newest_seen_question_id != null">
SELECT * FROM (
  -- select all questions that were created _after_
  -- the last seen max question id
  -- client should maintain this value as in get the 
  -- largest question id for every page and compare it
  -- with the current known max id. And client should
  -- do it for the whole duration of the paging
  -- and pass it to the follow up queries
  SELECT id, some_other_field, yet_another_field
  FROM question
  WHERE id > #{newest_seen_question_id}
  -- note that here we do sorting in the reverse order
  -- basically expanding the set of records that was returned
  -- in the direction of the future
  ORDER BY id ASC
  LIMIT #{limit}
  UNION ALL
</if>
  -- select all questions that were created _before_
  -- the last seen min question id.
  -- client should maintain this value for the whole
  -- duration of the paging
  -- and pass it to the follow up queries      
  SELECT id, some_other_field, yet_another_field
  FROM question
  <if test="oldest_seen_question_id != null">
    WHERE id < #{oldest_seen_question_id}
  </if>
  ORDER BY id DESC
  LIMIT #{limit}
<if test="newest_seen_question_id != null">
) AS q
ORDER BY id DESC
LIMIT #{limit}
</if>

Another benefit is that this approach to paging that does not use OFFSET is much better from performance standpoint.