MySQL won't use an index on an int, if I provide a string
Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:
# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';
This is a MYISAM table with lead_id as primary key
| Field | Type | Null | Key | Default |Extra |
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?
mysql index datatypes
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:
# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';
This is a MYISAM table with lead_id as primary key
| Field | Type | Null | Key | Default |Extra |
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?
mysql index datatypes
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1) Please runSHOW CREATE TABLE dial_listG2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???
– RolandoMySQLDBA
Sep 4 '13 at 19:43
4
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@dcaswell No, the question linked is about the opposite conversion:WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to useWHERE int_column = '100'.
– ypercubeᵀᴹ
Mar 27 '18 at 8:51
add a comment |
Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:
# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';
This is a MYISAM table with lead_id as primary key
| Field | Type | Null | Key | Default |Extra |
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?
mysql index datatypes
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log:
# Time: 130904 12:19:59
# User@Host: cron[cron] @ [10.71.41.38]
# Query_time: 58.488760 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 13634899
SET timestamp=1378311599;
UPDATE dial_list set status='AB' where lead_id = '3335602076';
This is a MYISAM table with lead_id as primary key
| Field | Type | Null | Key | Default |Extra |
| lead_id | int(9) unsigned | NO | PRI | NULL | auto_increment |
The same query run on an identical server with similar data size uses the index. Also, not all such queries on this server turn up as table scans. What could be the problem?
mysql index datatypes
mysql index datatypes
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
edited Jan 19 '18 at 4:46
Evan Carroll
31.6k965213
31.6k965213
asked Sep 4 '13 at 19:31
warmCwarmC
212
asked Sep 4 '13 at 19:31
warmCwarmC
212
asked Sep 4 '13 at 19:31
warmCwarmC
212
212
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 hours ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1) Please runSHOW CREATE TABLE dial_listG2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???
– RolandoMySQLDBA
Sep 4 '13 at 19:43
4
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@dcaswell No, the question linked is about the opposite conversion:WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to useWHERE int_column = '100'.
– ypercubeᵀᴹ
Mar 27 '18 at 8:51
add a comment |
1) Please runSHOW CREATE TABLE dial_listG2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???
– RolandoMySQLDBA
Sep 4 '13 at 19:43
4
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@dcaswell No, the question linked is about the opposite conversion:WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to useWHERE int_column = '100'.
– ypercubeᵀᴹ
Mar 27 '18 at 8:51
1) Please run
SHOW CREATE TABLE dial_listG 2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???– RolandoMySQLDBA
Sep 4 '13 at 19:43
1) Please run
SHOW CREATE TABLE dial_listG 2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???– RolandoMySQLDBA
Sep 4 '13 at 19:43
4
4
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@dcaswell No, the question linked is about the opposite conversion:
WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to use WHERE int_column = '100'.– ypercubeᵀᴹ
Mar 27 '18 at 8:51
@dcaswell No, the question linked is about the opposite conversion:
WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to use WHERE int_column = '100'.– ypercubeᵀᴹ
Mar 27 '18 at 8:51
add a comment |
1 Answer
1
active
oldest
votes
From the comments dcaswell,
Is the fact that you're comparing a string and int the issue
He suggests you check out
- http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f49279%2fmysql-wont-use-an-index-on-an-int-if-i-provide-a-string%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
From the comments dcaswell,
Is the fact that you're comparing a string and int the issue
He suggests you check out
- http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
add a comment |
From the comments dcaswell,
Is the fact that you're comparing a string and int the issue
He suggests you check out
- http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
add a comment |
From the comments dcaswell,
Is the fact that you're comparing a string and int the issue
He suggests you check out
- http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
From the comments dcaswell,
Is the fact that you're comparing a string and int the issue
He suggests you check out
- http://stackoverflow.com/questions/16786063/mysql-comparison-of-integer-value-and-string-field-with-index
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
answered Jan 19 '18 at 4:46
Evan CarrollEvan Carroll
31.6k965213
31.6k965213
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f49279%2fmysql-wont-use-an-index-on-an-int-if-i-provide-a-string%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
1) Please run
SHOW CREATE TABLE dial_listG2) Please tell me, does the table experience heavy INSERTs, UPDATEs, and DELETEs ???– RolandoMySQLDBA
Sep 4 '13 at 19:43
4
Is the fact that you're comparing a string and int the issue: stackoverflow.com/questions/16786063/…
– dcaswell
Sep 4 '13 at 20:55
@RolandoMySQLDBA yes, it expereinces heavy Inserts, Updates, Selects but not deletes. Inserts are only in beginning of day, and have not been done just before this query
– warmC
Sep 5 '13 at 4:02
@dcaswell No, the question linked is about the opposite conversion:
WHERE string_column = 100. The conversion used in this question wouldn't have this issue - even if it's not advised to useWHERE int_column = '100'.– ypercubeᵀᴹ
Mar 27 '18 at 8:51