Mixing
SQL - What's the faster and less greedy way to select last rows on a large table?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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?
mysql performance
asked Jan 3 at 11:04
SnyteSnyte
345
add a comment |
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?
mysql performance
asked Jan 3 at 11:04
SnyteSnyte
345
Not sure why you haveLIMIT 20,20surelyLIMIT 20would be the last 20 messages
– RiggsFolly
Jan 3 at 11:08
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
NoORDER BY ... DESCreturns the results sorted into Newest first, so you only need the first 20 rows
– RiggsFolly
Jan 3 at 11:43
add a comment |
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?
mysql performance
asked Jan 3 at 11:04
SnyteSnyte
345
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?
mysql performance
mysql performance
asked Jan 3 at 11:04
SnyteSnyte
345
asked Jan 3 at 11:04
SnyteSnyte
345
asked Jan 3 at 11:04
SnyteSnyte
345
asked Jan 3 at 11:04
SnyteSnyte
345
asked Jan 3 at 11:04
SnyteSnyte
345
345
Not sure why you haveLIMIT 20,20surelyLIMIT 20would be the last 20 messages
– RiggsFolly
Jan 3 at 11:08
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
NoORDER BY ... DESCreturns the results sorted into Newest first, so you only need the first 20 rows
– RiggsFolly
Jan 3 at 11:43
add a comment |
Not sure why you haveLIMIT 20,20surelyLIMIT 20would be the last 20 messages
– RiggsFolly
Jan 3 at 11:08
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
NoORDER BY ... DESCreturns the results sorted into Newest first, so you only need the first 20 rows
– RiggsFolly
Jan 3 at 11:43
Not sure why you have
LIMIT 20,20 surely LIMIT 20 would be the last 20 messages– RiggsFolly
Jan 3 at 11:08
Not sure why you have
LIMIT 20,20 surely LIMIT 20 would be the last 20 messages– RiggsFolly
Jan 3 at 11:08
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
No
ORDER BY ... DESC returns the results sorted into Newest first, so you only need the first 20 rows– RiggsFolly
Jan 3 at 11:43
No
ORDER BY ... DESC returns the results sorted into Newest first, so you only need the first 20 rows– RiggsFolly
Jan 3 at 11:43
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
For more discussion of "remembering where you left off" instead of usingOFFSET, see mysql.rjweb.org/doc.php/pagination
– Rick James
Jan 4 at 1:12
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021061%2fsql-whats-the-faster-and-less-greedy-way-to-select-last-rows-on-a-large-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
For more discussion of "remembering where you left off" instead of usingOFFSET, see mysql.rjweb.org/doc.php/pagination
– Rick James
Jan 4 at 1:12
add a comment |
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
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
For more discussion of "remembering where you left off" instead of usingOFFSET, see mysql.rjweb.org/doc.php/pagination
– Rick James
Jan 4 at 1:12
add a comment |
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
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
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
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
answered Jan 3 at 11:12
Carlos Alves JorgeCarlos Alves Jorge
586215
586215
For more discussion of "remembering where you left off" instead of usingOFFSET, see mysql.rjweb.org/doc.php/pagination
– Rick James
Jan 4 at 1:12
add a comment |
For more discussion of "remembering where you left off" instead of usingOFFSET, see mysql.rjweb.org/doc.php/pagination
– Rick James
Jan 4 at 1:12
For more discussion of "remembering where you left off" instead of using
OFFSET, see mysql.rjweb.org/doc.php/pagination– Rick James
Jan 4 at 1:12
For more discussion of "remembering where you left off" instead of using
OFFSET, see mysql.rjweb.org/doc.php/pagination– Rick James
Jan 4 at 1:12
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021061%2fsql-whats-the-faster-and-less-greedy-way-to-select-last-rows-on-a-large-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Not sure why you have
LIMIT 20,20surelyLIMIT 20would be the last 20 messages– RiggsFolly
Jan 3 at 11:08
Yes, but if I want to get then messages from 20 to 0 id, I would need to specify the offset, right?
– Snyte
Jan 3 at 11:13
No
ORDER BY ... DESCreturns the results sorted into Newest first, so you only need the first 20 rows– RiggsFolly
Jan 3 at 11:43