Mixing
Regenerate indexes after changing length of VARCHAR field
I have an InnoDB table in my MySQL database. In it, I have a field (not a primary key) that is VARCHAR(20). And it is indexed.
But recently, I had to increase the size to 75. That is, VARCHAR(75)
So, do I have to do anything to re-index this field? Or does the indexes gets updated automatically or won't be affected?
At the moment, this table contains only around 2000 rows.
Also, what if it was the PRIMARY KEY field which I want to increase the size ? That is, suppose my primary key is VARCHAR and wish to increase the field length.
mysql indexing database-indexes
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
add a comment |
I have an InnoDB table in my MySQL database. In it, I have a field (not a primary key) that is VARCHAR(20). And it is indexed.
But recently, I had to increase the size to 75. That is, VARCHAR(75)
So, do I have to do anything to re-index this field? Or does the indexes gets updated automatically or won't be affected?
At the moment, this table contains only around 2000 rows.
Also, what if it was the PRIMARY KEY field which I want to increase the size ? That is, suppose my primary key is VARCHAR and wish to increase the field length.
mysql indexing database-indexes
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
add a comment |
I have an InnoDB table in my MySQL database. In it, I have a field (not a primary key) that is VARCHAR(20). And it is indexed.
But recently, I had to increase the size to 75. That is, VARCHAR(75)
So, do I have to do anything to re-index this field? Or does the indexes gets updated automatically or won't be affected?
At the moment, this table contains only around 2000 rows.
Also, what if it was the PRIMARY KEY field which I want to increase the size ? That is, suppose my primary key is VARCHAR and wish to increase the field length.
mysql indexing database-indexes
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
I have an InnoDB table in my MySQL database. In it, I have a field (not a primary key) that is VARCHAR(20). And it is indexed.
But recently, I had to increase the size to 75. That is, VARCHAR(75)
So, do I have to do anything to re-index this field? Or does the indexes gets updated automatically or won't be affected?
At the moment, this table contains only around 2000 rows.
Also, what if it was the PRIMARY KEY field which I want to increase the size ? That is, suppose my primary key is VARCHAR and wish to increase the field length.
mysql indexing database-indexes
mysql indexing database-indexes
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
asked Jan 1 at 14:13
Vpp ManVpp Man
81842453
81842453
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Increasing size of varchar does not affect indicex. You can continue using without re-creating your indices.
answered Jan 1 at 14:16
SimonareSimonare
15k11840
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use theOPTIMIZE TABLE tablenamefor this?
– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
OPTIMIZE TABLEis almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.
– Rick James
Jan 2 at 4:08
add a comment |
The indexes are updated when you change column definitions.
ANALYZE TABLE mytable; is always a good idea after making major changes to a table; it updates index statistics so the query planner can do the best possible job.
Major changes include many inserts, deletes, or updates to indexed columns, as well as ALTERing the table.
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
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%2f53996162%2fregenerate-indexes-after-changing-length-of-varchar-field%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
Increasing size of varchar does not affect indicex. You can continue using without re-creating your indices.
answered Jan 1 at 14:16
SimonareSimonare
15k11840
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use theOPTIMIZE TABLE tablenamefor this?
– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
OPTIMIZE TABLEis almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.
– Rick James
Jan 2 at 4:08
add a comment |
Increasing size of varchar does not affect indicex. You can continue using without re-creating your indices.
answered Jan 1 at 14:16
SimonareSimonare
15k11840
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use theOPTIMIZE TABLE tablenamefor this?
– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
OPTIMIZE TABLEis almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.
– Rick James
Jan 2 at 4:08
add a comment |
Increasing size of varchar does not affect indicex. You can continue using without re-creating your indices.
answered Jan 1 at 14:16
SimonareSimonare
15k11840
Increasing size of varchar does not affect indicex. You can continue using without re-creating your indices.
answered Jan 1 at 14:16
SimonareSimonare
15k11840
answered Jan 1 at 14:16
SimonareSimonare
15k11840
answered Jan 1 at 14:16
SimonareSimonare
15k11840
answered Jan 1 at 14:16
SimonareSimonare
15k11840
15k11840
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use theOPTIMIZE TABLE tablenamefor this?
– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
OPTIMIZE TABLEis almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.
– Rick James
Jan 2 at 4:08
add a comment |
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use theOPTIMIZE TABLE tablenamefor this?
– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
OPTIMIZE TABLEis almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.
– Rick James
Jan 2 at 4:08
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use the
OPTIMIZE TABLE tablename for this?– Vpp Man
Jan 1 at 14:39
Thank you. So, I have to bother only if the length is decreased or the datatype is changed? In such cases, I have to use the
OPTIMIZE TABLE tablename for this?– Vpp Man
Jan 1 at 14:39
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
if you decrease column size you may need to be carefull since it may end up with data loss. and yes you may need to optimize your table or recreate your indices. You need to check whether your index is fragmented or not. check this : serverfault.com/a/265885/458296
– Simonare
Jan 1 at 14:43
1
1
OPTIMIZE TABLE is almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.– Rick James
Jan 2 at 4:08
OPTIMIZE TABLE is almost never necessary for an InnoDB table. Furthermore, for a "tiny" 2000-row table, defrag, poor stats, etc, are not worth worrying about.– Rick James
Jan 2 at 4:08
add a comment |
The indexes are updated when you change column definitions.
ANALYZE TABLE mytable; is always a good idea after making major changes to a table; it updates index statistics so the query planner can do the best possible job.
Major changes include many inserts, deletes, or updates to indexed columns, as well as ALTERing the table.
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
add a comment |
The indexes are updated when you change column definitions.
ANALYZE TABLE mytable; is always a good idea after making major changes to a table; it updates index statistics so the query planner can do the best possible job.
Major changes include many inserts, deletes, or updates to indexed columns, as well as ALTERing the table.
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
add a comment |
The indexes are updated when you change column definitions.
ANALYZE TABLE mytable; is always a good idea after making major changes to a table; it updates index statistics so the query planner can do the best possible job.
Major changes include many inserts, deletes, or updates to indexed columns, as well as ALTERing the table.
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
The indexes are updated when you change column definitions.
ANALYZE TABLE mytable; is always a good idea after making major changes to a table; it updates index statistics so the query planner can do the best possible job.
Major changes include many inserts, deletes, or updates to indexed columns, as well as ALTERing the table.
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
answered Jan 1 at 14:42
O. JonesO. Jones
60.2k974107
60.2k974107
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%2f53996162%2fregenerate-indexes-after-changing-length-of-varchar-field%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
