How to use Limit in query on my index column without scan all rows?
6
Here is my table :
In my table
Clustering_key(Primary key and auto incremental)
ID(Index Column)
Data(Text datatype column)
Position(Index column) maintain the order ofData
My table have 90,000 rows with same ID equal to 5. I want to first 3 rows with ID equal to 5 and my query like this
Select * from mytable where ID=5 Limit 3;
ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.
Here Explain query :
Any possibility to avoid all rows scan.
Please give me some solution
Thanks in advance
mysql database innodb
asked Nov 17 '18 at 10:55
ShubhamShubham
819
|
show 9 more comments
6
Here is my table :
In my table
Clustering_key(Primary key and auto incremental)
ID(Index Column)
Data(Text datatype column)
Position(Index column) maintain the order ofData
My table have 90,000 rows with same ID equal to 5. I want to first 3 rows with ID equal to 5 and my query like this
Select * from mytable where ID=5 Limit 3;
ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.
Here Explain query :
Any possibility to avoid all rows scan.
Please give me some solution
Thanks in advance
mysql database innodb
asked Nov 17 '18 at 10:55
ShubhamShubham
819
2
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
MySQL has to scan all the row because ofOrder By Positionas it needs to first sort them all. Try adding a composite index (id,position)
– Madhur Bhaiya
Nov 17 '18 at 11:00
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i usePositionin where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;
– Shubham
Nov 17 '18 at 13:05
@Madhur I removeOrder By Positionin my query but result are same . I think reason of scan all rows isID=5but i am using index on this column.
– Shubham
Nov 17 '18 at 13:15
@Shubham please addEXPLAINstatement results to the question.
– Madhur Bhaiya
Nov 17 '18 at 13:17
|
show 9 more comments
6
6
6
1
Here is my table :
In my table
Clustering_key(Primary key and auto incremental)
ID(Index Column)
Data(Text datatype column)
Position(Index column) maintain the order ofData
My table have 90,000 rows with same ID equal to 5. I want to first 3 rows with ID equal to 5 and my query like this
Select * from mytable where ID=5 Limit 3;
ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.
Here Explain query :
Any possibility to avoid all rows scan.
Please give me some solution
Thanks in advance
mysql database innodb
asked Nov 17 '18 at 10:55
ShubhamShubham
819
Here is my table :
In my table
Clustering_key(Primary key and auto incremental)
ID(Index Column)
Data(Text datatype column)
Position(Index column) maintain the order ofData
My table have 90,000 rows with same ID equal to 5. I want to first 3 rows with ID equal to 5 and my query like this
Select * from mytable where ID=5 Limit 3;
ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.
Here Explain query :
Any possibility to avoid all rows scan.
Please give me some solution
Thanks in advance
mysql database innodb
mysql database innodb
asked Nov 17 '18 at 10:55
ShubhamShubham
819
asked Nov 17 '18 at 10:55
ShubhamShubham
819
edited Nov 21 '18 at 6:40
Shubham
asked Nov 17 '18 at 10:55
ShubhamShubham
819
asked Nov 17 '18 at 10:55
ShubhamShubham
819
asked Nov 17 '18 at 10:55
ShubhamShubham
819
819
2
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
MySQL has to scan all the row because ofOrder By Positionas it needs to first sort them all. Try adding a composite index (id,position)
– Madhur Bhaiya
Nov 17 '18 at 11:00
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i usePositionin where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;
– Shubham
Nov 17 '18 at 13:05
@Madhur I removeOrder By Positionin my query but result are same . I think reason of scan all rows isID=5but i am using index on this column.
– Shubham
Nov 17 '18 at 13:15
@Shubham please addEXPLAINstatement results to the question.
– Madhur Bhaiya
Nov 17 '18 at 13:17
|
show 9 more comments
2
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
MySQL has to scan all the row because ofOrder By Positionas it needs to first sort them all. Try adding a composite index (id,position)
– Madhur Bhaiya
Nov 17 '18 at 11:00
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i usePositionin where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;
– Shubham
Nov 17 '18 at 13:05
@Madhur I removeOrder By Positionin my query but result are same . I think reason of scan all rows isID=5but i am using index on this column.
– Shubham
Nov 17 '18 at 13:15
@Shubham please addEXPLAINstatement results to the question.
– Madhur Bhaiya
Nov 17 '18 at 13:17
2
2
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
MySQL has to scan all the row because of
Order By Position as it needs to first sort them all. Try adding a composite index (id, position)– Madhur Bhaiya
Nov 17 '18 at 11:00
MySQL has to scan all the row because of
Order By Position as it needs to first sort them all. Try adding a composite index (id, position)– Madhur Bhaiya
Nov 17 '18 at 11:00
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i use
Position in where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;– Shubham
Nov 17 '18 at 13:05
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i use
Position in where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;– Shubham
Nov 17 '18 at 13:05
@Madhur I remove
Order By Position in my query but result are same . I think reason of scan all rows is ID=5 but i am using index on this column.– Shubham
Nov 17 '18 at 13:15
@Madhur I remove
Order By Position in my query but result are same . I think reason of scan all rows is ID=5 but i am using index on this column.– Shubham
Nov 17 '18 at 13:15
@Shubham please add
EXPLAIN statement results to the question.– Madhur Bhaiya
Nov 17 '18 at 13:17
@Shubham please add
EXPLAIN statement results to the question.– Madhur Bhaiya
Nov 17 '18 at 13:17
|
show 9 more comments
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53350515%2fhow-to-use-limit-in-query-on-my-index-column-without-scan-all-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53350515%2fhow-to-use-limit-in-query-on-my-index-column-without-scan-all-rows%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
2
What is your PRIMARY KEY? (It sounds like it should be a combination of id and position), and that the separate index on id is therefore redundant.
– Strawberry
Nov 17 '18 at 10:59
MySQL has to scan all the row because of
Order By Positionas it needs to first sort them all. Try adding a composite index (id,position)– Madhur Bhaiya
Nov 17 '18 at 11:00
@Madhur I try this but its doesn't affect the results. if i execute same query. This composite key is useful when i use
Positionin where clause like Select * from mytable where ID=5 and Position> xyz and Position < xyz;– Shubham
Nov 17 '18 at 13:05
@Madhur I remove
Order By Positionin my query but result are same . I think reason of scan all rows isID=5but i am using index on this column.– Shubham
Nov 17 '18 at 13:15
@Shubham please add
EXPLAINstatement results to the question.– Madhur Bhaiya
Nov 17 '18 at 13:17