CODEWITHSUNDEEP
mysqlperformance
Snyte
Snyte

Reputation: 93

SQL - What's the faster and less greedy way to select last rows on a large table?

I have a large messages table. What I want to do to improve performances and page loading times is to get last posted messages, let's say, the 20 last messages.

When users will scroll down, previous 20 messages will be added to the DOM thanks to JS.

So if I have 40 messages in total, messages 40 to 20 will be shown. User will scroll down, 20 to 0 messages will then be added to the DOM.

Problem is, if I store the lowest id of my loaded messages and use a query like this :

SELECT * FROM messages ORDER BY id DESC LIMIT 20,20 (<= is the lowest offset of loaded messages)

I read that MySQL will first fetch every single row of the table, then just return the entries I want, which, I guess, would take quite some time if my messages table would contain 500 000 entries.

What's the most efficient and faster way to do this?

Upvotes: 0

Views: 120

Answers (1)

Carlos Alves Jorge
Carlos Alves Jorge

Reputation: 1985

So basically what you want to do is called "Lazy Loading" as of it will only load records when it will need to do so.

The best way is to send the last id of a loaded record. Let's say it was id 1988.

Then you can query like 

SELECT * FROM messages WHERE id < 1988 ORDER BY id DESC LIMIT 20

next query would probably look like

SELECT * FROM messages WHERE id < 1968 ORDER BY id DESC LIMIT 20

and so on

Upvotes: 1

Related Questions