How to add column to a table which contain millions of dataDatabase, Table and Column Naming Conventions?MyISAM versus InnoDBHow to list the tables in a SQLite database file that was opened with ATTACH?How to find all the tables in MySQL with specific column names in them?Which MySQL data type to use for storing boolean valuesBest way to store password in databaseDuplicating a MySQL table, indices, and dataHow to import CSV file to MySQL tableWhat are the options for storing hierarchical data in a relational database?SQL select only rows with max value on a column
Bent arrow under a node
I have found ports on my Samsung smart tv running a display service. What can I do with it?
I calculated that we should be able to see the sun well beyond the observable universe. Where did I go wrong?
What is this word in a sample of blackletter script?
Why is Havana covered in 5-digit numbers in Our Man in Havana?
In the US, can a former president run again?
Is there any possible way to get these hearts as Adult Link?
Leaving job close to major deadlines
Does Snape have a bad hairstyle according to the wizarding world?
Math symbols in math operators
Make symbols atomic, without losing their type
How are で and いう being used in this context?
What kind of chart is this?
Negation of a verb in the "passé Composé" and used with a "COI"
"Correct me if I'm wrong"
How to modify a string without altering its text properties
Why does a Force divides equally on a Multiple Support/Legs?
How can I prevent a user from copying files on another hard drive?
Are there any individual aliens that have gained superpowers in the Marvel universe?
How useful is the GRE Exam?
First occurrence in the Sixers sequence
Why are there no file insertion syscalls
What does this Swiss black on yellow rectangular traffic sign with a symbol looking like a dart mean?
Do details of my undergraduate title matter?
How to add column to a table which contain millions of data
Database, Table and Column Naming Conventions?MyISAM versus InnoDBHow to list the tables in a SQLite database file that was opened with ATTACH?How to find all the tables in MySQL with specific column names in them?Which MySQL data type to use for storing boolean valuesBest way to store password in databaseDuplicating a MySQL table, indices, and dataHow to import CSV file to MySQL tableWhat are the options for storing hierarchical data in a relational database?SQL select only rows with max value on a column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I need to add an additional column to live DB table which contain millions of data. I'm using maria 10.0. Is there any tools? or Can anyone suggest a better method for this process.
mysql database mariadb
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
|
show 2 more comments
I need to add an additional column to live DB table which contain millions of data. I'm using maria 10.0. Is there any tools? or Can anyone suggest a better method for this process.
mysql database mariadb
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
1
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35
|
show 2 more comments
I need to add an additional column to live DB table which contain millions of data. I'm using maria 10.0. Is there any tools? or Can anyone suggest a better method for this process.
mysql database mariadb
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
I need to add an additional column to live DB table which contain millions of data. I'm using maria 10.0. Is there any tools? or Can anyone suggest a better method for this process.
mysql database mariadb
mysql database mariadb
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
asked Mar 25 at 5:58
Prasanna DeshappriyaPrasanna Deshappriya
61
61
1
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35
|
show 2 more comments
1
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35
1
1
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35
|
show 2 more comments
1 Answer
1
active
oldest
votes
MariaDB 10.3 has an "instand add column" feature that makes such an operation really fast.
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
As MariaDB 10.0 reaches its end of life ("EOL") this month, an update to a more recent release should be considered now, anyway?
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
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%2f55331976%2fhow-to-add-column-to-a-table-which-contain-millions-of-data%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
MariaDB 10.3 has an "instand add column" feature that makes such an operation really fast.
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
As MariaDB 10.0 reaches its end of life ("EOL") this month, an update to a more recent release should be considered now, anyway?
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
add a comment |
MariaDB 10.3 has an "instand add column" feature that makes such an operation really fast.
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
As MariaDB 10.0 reaches its end of life ("EOL") this month, an update to a more recent release should be considered now, anyway?
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
add a comment |
MariaDB 10.3 has an "instand add column" feature that makes such an operation really fast.
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
As MariaDB 10.0 reaches its end of life ("EOL") this month, an update to a more recent release should be considered now, anyway?
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
MariaDB 10.3 has an "instand add column" feature that makes such an operation really fast.
https://mariadb.com/kb/en/library/instant-add-column-for-innodb/
As MariaDB 10.0 reaches its end of life ("EOL") this month, an update to a more recent release should be considered now, anyway?
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
answered Mar 25 at 20:55
Hartmut HolzgraefeHartmut Holzgraefe
1,689712
1,689712
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
add a comment |
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
MySQL 8.0 also has the instant add column. This feature is an example of someone not working for Oracle or MariaDB contributing the code.
– Rick James
May 7 at 6:10
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
Right ... I had this question in my "mariadb" tag stream, didn't notice that it was also tagged "mysql", too
– Hartmut Holzgraefe
May 8 at 8:27
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%2f55331976%2fhow-to-add-column-to-a-table-which-contain-millions-of-data%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
I don't really see much of a way to speed this up, other then the possible issue of the indices which already exist on your table. If adding a new column would force an index rebuild, maybe you can look into turning off/dropping the indices before you add the new column.
– Tim Biegeleisen
Mar 25 at 6:02
percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime
– Pradeep
Mar 25 at 6:03
I'm not saying it's a great idea, but you coukd construct an annexe table that simply joins to your existing table in a 1-1 relationship
– Strawberry
Mar 25 at 6:17
@Strawberry If I construct an annexe table, I have to put additional join which will cause slow down the other processes.
– Prasanna Deshappriya
Mar 25 at 7:11
Is that a statement or a question?
– Strawberry
Mar 25 at 7:35