optimize mysql - will index improve in this case?
Column alo is tinytext (average length: two chars)
My most often query is
select * from table where .... order by alo=''
Will it be faster when I make an index on alo?
The exactly content of alo does not matter - ordering is only based on the question if alo is empty or not.
Why does it (not) improve speed?
mysql optimization indexing
edited Nov 24 '18 at 5:44
lagom
1
asked Nov 23 '18 at 23:44
PeterPeter
41
add a comment |
Column alo is tinytext (average length: two chars)
My most often query is
select * from table where .... order by alo=''
Will it be faster when I make an index on alo?
The exactly content of alo does not matter - ordering is only based on the question if alo is empty or not.
Why does it (not) improve speed?
mysql optimization indexing
edited Nov 24 '18 at 5:44
lagom
1
asked Nov 23 '18 at 23:44
PeterPeter
41
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
order by alowill definitively be faster ifalois indexed,order by alo=''however I doubt. The result of this operation isn't indexed, nothing computed at run time is.
– Havenard
Nov 24 '18 at 6:01
add a comment |
Column alo is tinytext (average length: two chars)
My most often query is
select * from table where .... order by alo=''
Will it be faster when I make an index on alo?
The exactly content of alo does not matter - ordering is only based on the question if alo is empty or not.
Why does it (not) improve speed?
mysql optimization indexing
edited Nov 24 '18 at 5:44
lagom
1
asked Nov 23 '18 at 23:44
PeterPeter
41
Column alo is tinytext (average length: two chars)
My most often query is
select * from table where .... order by alo=''
Will it be faster when I make an index on alo?
The exactly content of alo does not matter - ordering is only based on the question if alo is empty or not.
Why does it (not) improve speed?
mysql optimization indexing
mysql optimization indexing
edited Nov 24 '18 at 5:44
lagom
1
asked Nov 23 '18 at 23:44
PeterPeter
41
edited Nov 24 '18 at 5:44
lagom
1
asked Nov 23 '18 at 23:44
PeterPeter
41
edited Nov 24 '18 at 5:44
lagom
1
edited Nov 24 '18 at 5:44
lagom
1
edited Nov 24 '18 at 5:44
lagom
1
1
asked Nov 23 '18 at 23:44
PeterPeter
41
asked Nov 23 '18 at 23:44
PeterPeter
41
asked Nov 23 '18 at 23:44
PeterPeter
41
41
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
order by alowill definitively be faster ifalois indexed,order by alo=''however I doubt. The result of this operation isn't indexed, nothing computed at run time is.
– Havenard
Nov 24 '18 at 6:01
add a comment |
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
order by alowill definitively be faster ifalois indexed,order by alo=''however I doubt. The result of this operation isn't indexed, nothing computed at run time is.
– Havenard
Nov 24 '18 at 6:01
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
order by alo will definitively be faster if alo is indexed, order by alo='' however I doubt. The result of this operation isn't indexed, nothing computed at run time is.– Havenard
Nov 24 '18 at 6:01
order by alo will definitively be faster if alo is indexed, order by alo='' however I doubt. The result of this operation isn't indexed, nothing computed at run time is.– Havenard
Nov 24 '18 at 6:01
add a comment |
1 Answer
1
active
oldest
votes
I will assume we are talking only about
select * from table where .... order by alo=''
Case 1: No index. The entire table will always be scanned.
Case 2: Something in the WHERE could use an index. Then that index may help.
Case 3: alo is not mentioned in the WHERE. Indexing alo will not help. MySQL cannot use an index when the indexed column is hiding in a function. In this query, alo='' is effectively a function call.
Saying ORDER BY alo could use INDEX(alo). And it would have similar results. Well, actually alo='' sorts blanks after non-blanks. So you might need ORDER BY alo DESC to get the blanks first. Furthermore, NULL values may add another wrinkle.
Meanwhile, there is another optimization... Don't use TINYTEXT; instead, use VARCHAR(..) with a suitable max. (The reason has to do with temp tables in complex queries, and may not matter for your query.) Also, you cannot index any kind of TEXT column.
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
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%2f53453958%2foptimize-mysql-will-index-improve-in-this-case%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
I will assume we are talking only about
select * from table where .... order by alo=''
Case 1: No index. The entire table will always be scanned.
Case 2: Something in the WHERE could use an index. Then that index may help.
Case 3: alo is not mentioned in the WHERE. Indexing alo will not help. MySQL cannot use an index when the indexed column is hiding in a function. In this query, alo='' is effectively a function call.
Saying ORDER BY alo could use INDEX(alo). And it would have similar results. Well, actually alo='' sorts blanks after non-blanks. So you might need ORDER BY alo DESC to get the blanks first. Furthermore, NULL values may add another wrinkle.
Meanwhile, there is another optimization... Don't use TINYTEXT; instead, use VARCHAR(..) with a suitable max. (The reason has to do with temp tables in complex queries, and may not matter for your query.) Also, you cannot index any kind of TEXT column.
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
add a comment |
I will assume we are talking only about
select * from table where .... order by alo=''
Case 1: No index. The entire table will always be scanned.
Case 2: Something in the WHERE could use an index. Then that index may help.
Case 3: alo is not mentioned in the WHERE. Indexing alo will not help. MySQL cannot use an index when the indexed column is hiding in a function. In this query, alo='' is effectively a function call.
Saying ORDER BY alo could use INDEX(alo). And it would have similar results. Well, actually alo='' sorts blanks after non-blanks. So you might need ORDER BY alo DESC to get the blanks first. Furthermore, NULL values may add another wrinkle.
Meanwhile, there is another optimization... Don't use TINYTEXT; instead, use VARCHAR(..) with a suitable max. (The reason has to do with temp tables in complex queries, and may not matter for your query.) Also, you cannot index any kind of TEXT column.
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
add a comment |
I will assume we are talking only about
select * from table where .... order by alo=''
Case 1: No index. The entire table will always be scanned.
Case 2: Something in the WHERE could use an index. Then that index may help.
Case 3: alo is not mentioned in the WHERE. Indexing alo will not help. MySQL cannot use an index when the indexed column is hiding in a function. In this query, alo='' is effectively a function call.
Saying ORDER BY alo could use INDEX(alo). And it would have similar results. Well, actually alo='' sorts blanks after non-blanks. So you might need ORDER BY alo DESC to get the blanks first. Furthermore, NULL values may add another wrinkle.
Meanwhile, there is another optimization... Don't use TINYTEXT; instead, use VARCHAR(..) with a suitable max. (The reason has to do with temp tables in complex queries, and may not matter for your query.) Also, you cannot index any kind of TEXT column.
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
I will assume we are talking only about
select * from table where .... order by alo=''
Case 1: No index. The entire table will always be scanned.
Case 2: Something in the WHERE could use an index. Then that index may help.
Case 3: alo is not mentioned in the WHERE. Indexing alo will not help. MySQL cannot use an index when the indexed column is hiding in a function. In this query, alo='' is effectively a function call.
Saying ORDER BY alo could use INDEX(alo). And it would have similar results. Well, actually alo='' sorts blanks after non-blanks. So you might need ORDER BY alo DESC to get the blanks first. Furthermore, NULL values may add another wrinkle.
Meanwhile, there is another optimization... Don't use TINYTEXT; instead, use VARCHAR(..) with a suitable max. (The reason has to do with temp tables in complex queries, and may not matter for your query.) Also, you cannot index any kind of TEXT column.
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
answered Nov 25 '18 at 5:52
Rick JamesRick James
69k561101
69k561101
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%2f53453958%2foptimize-mysql-will-index-improve-in-this-case%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
We don't have enough information to know. Many things affect the execution speed of a query. Indexes are one of them. We need to know much more if we are to help you.
– Andy Lester
Nov 24 '18 at 1:53
order by alowill definitively be faster ifalois indexed,order by alo=''however I doubt. The result of this operation isn't indexed, nothing computed at run time is.– Havenard
Nov 24 '18 at 6:01