Are multiple WHERE/AND clauses in MySQL checked sequentially? And are subsequent checks skipped if the first...
Scenario
Let's say I have a MySQL query that contains multiple WHERE/AND clauses.
For for instance, say I have the query
SELECT * FROM some_table
WHERE col1 = 5
AND col2 = 9
AND col3 LIKE '%string%'
Question
Is the col1 = 5 check done as the first in sequence here, since it's written first? And more importantly, are the other two checks skipped if col1 != 5?
The reason I ask is that the third clause, col3 LIKE '%string3%, will take more time to run, and I'm wondering if it makes sense to put it last, since I don't want to run it if one of the first two checks are false.
mysql performance
asked Nov 23 '18 at 13:47
AlecAlec
740828
add a comment |
Scenario
Let's say I have a MySQL query that contains multiple WHERE/AND clauses.
For for instance, say I have the query
SELECT * FROM some_table
WHERE col1 = 5
AND col2 = 9
AND col3 LIKE '%string%'
Question
Is the col1 = 5 check done as the first in sequence here, since it's written first? And more importantly, are the other two checks skipped if col1 != 5?
The reason I ask is that the third clause, col3 LIKE '%string3%, will take more time to run, and I'm wondering if it makes sense to put it last, since I don't want to run it if one of the first two checks are false.
mysql performance
asked Nov 23 '18 at 13:47
AlecAlec
740828
add a comment |
Scenario
Let's say I have a MySQL query that contains multiple WHERE/AND clauses.
For for instance, say I have the query
SELECT * FROM some_table
WHERE col1 = 5
AND col2 = 9
AND col3 LIKE '%string%'
Question
Is the col1 = 5 check done as the first in sequence here, since it's written first? And more importantly, are the other two checks skipped if col1 != 5?
The reason I ask is that the third clause, col3 LIKE '%string3%, will take more time to run, and I'm wondering if it makes sense to put it last, since I don't want to run it if one of the first two checks are false.
mysql performance
asked Nov 23 '18 at 13:47
AlecAlec
740828
Scenario
Let's say I have a MySQL query that contains multiple WHERE/AND clauses.
For for instance, say I have the query
SELECT * FROM some_table
WHERE col1 = 5
AND col2 = 9
AND col3 LIKE '%string%'
Question
Is the col1 = 5 check done as the first in sequence here, since it's written first? And more importantly, are the other two checks skipped if col1 != 5?
The reason I ask is that the third clause, col3 LIKE '%string3%, will take more time to run, and I'm wondering if it makes sense to put it last, since I don't want to run it if one of the first two checks are false.
mysql performance
mysql performance
asked Nov 23 '18 at 13:47
AlecAlec
740828
asked Nov 23 '18 at 13:47
AlecAlec
740828
asked Nov 23 '18 at 13:47
AlecAlec
740828
asked Nov 23 '18 at 13:47
AlecAlec
740828
asked Nov 23 '18 at 13:47
AlecAlec
740828
740828
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
add a comment |
The optimal index for that query is
INDEX(col1, col2) -- in either order
Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.
Often, it is better (though not identical) to use FULLTEXT(col3) and have
WHERE col1 = 5
AND col2 = 9
AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)
In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.
The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.
If you don't have any suitable indexes, well, shame on you.
There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
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%2f53447902%2fare-multiple-where-and-clauses-in-mysql-checked-sequentially-and-are-subsequent%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
add a comment |
The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
add a comment |
The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
The SQL optimizer looks at the query at whole and tries to determine the most optimal query plan for the query. The order of the contitions in where-clause does not matter.
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
answered Nov 23 '18 at 14:39
slaaksoslaakso
2,946818
2,946818
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
add a comment |
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
That's exactly what I wanted to hear, thanks! I couldn't find this info in the docs though. Can you point me to it?
– Alec
Nov 23 '18 at 15:40
add a comment |
The optimal index for that query is
INDEX(col1, col2) -- in either order
Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.
Often, it is better (though not identical) to use FULLTEXT(col3) and have
WHERE col1 = 5
AND col2 = 9
AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)
In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.
The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.
If you don't have any suitable indexes, well, shame on you.
There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
add a comment |
The optimal index for that query is
INDEX(col1, col2) -- in either order
Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.
Often, it is better (though not identical) to use FULLTEXT(col3) and have
WHERE col1 = 5
AND col2 = 9
AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)
In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.
The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.
If you don't have any suitable indexes, well, shame on you.
There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
add a comment |
The optimal index for that query is
INDEX(col1, col2) -- in either order
Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.
Often, it is better (though not identical) to use FULLTEXT(col3) and have
WHERE col1 = 5
AND col2 = 9
AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)
In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.
The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.
If you don't have any suitable indexes, well, shame on you.
There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
The optimal index for that query is
INDEX(col1, col2) -- in either order
Given that, it will definitely check both col1 and col2 simultaneously in order to whittle down the number of rows to a much smaller number. Hence the LIKE will happen only for the few rows that match both col1 and col2. This order of actions is very likely to be optimal.
Often, it is better (though not identical) to use FULLTEXT(col3) and have
WHERE col1 = 5
AND col2 = 9
AND MATCH(col3) AGAINST ("+string" IN BOOLEAN MODE)
In this case, I am pretty sure it will start with the FULLTEXT index to test col3, hoping to get very few rows to double check against the other clauses. Because of various other issues, this is optimal. Any index(es) on col1 and col2 will not be used.
The general statement (so far) is: The Optimizer will pick AND clause(s) that it can use for one INDEX first. In that sense, the order of the AND clauses is violated -- as an optimization.
If you don't have any suitable indexes, well, shame on you.
There are many possibilities. I will be happy to discuss individual queries, but it will be hard to make too many generalities.
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
answered Nov 23 '18 at 21:50
Rick JamesRick James
66.6k55899
66.6k55899
add a comment |
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%2f53447902%2fare-multiple-where-and-clauses-in-mysql-checked-sequentially-and-are-subsequent%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
