mysql:how to insert into a table with many index faster?
I have a partition table with about 50 indexs and I will insert about 20 million
records every day.
Every time I call insert into tableA values (..),(..)... to insert 2000 records.this will take about five hours.
I can't load data from file. So I create a table talbeB as same as tableA, but tableB don't have index. First I insert data into tableB, then I call insert into tableA select * from tableB where mydate = 20181119. the first stage cost about 40 minutes, the second stage about one hour.
I want to know if have a method faster. Thanks a lot!
mysql indexing
asked Nov 19 at 11:31
Z.Lun
3719
|
show 7 more comments
I have a partition table with about 50 indexs and I will insert about 20 million
records every day.
Every time I call insert into tableA values (..),(..)... to insert 2000 records.this will take about five hours.
I can't load data from file. So I create a table talbeB as same as tableA, but tableB don't have index. First I insert data into tableB, then I call insert into tableA select * from tableB where mydate = 20181119. the first stage cost about 40 minutes, the second stage about one hour.
I want to know if have a method faster. Thanks a lot!
mysql indexing
asked Nov 19 at 11:31
Z.Lun
3719
1
50 indexes?Show theSHOW CREATE TABLE tablestatement maybe you can drop some??
– Raymond Nijland
Nov 19 at 11:33
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
1
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35
|
show 7 more comments
I have a partition table with about 50 indexs and I will insert about 20 million
records every day.
Every time I call insert into tableA values (..),(..)... to insert 2000 records.this will take about five hours.
I can't load data from file. So I create a table talbeB as same as tableA, but tableB don't have index. First I insert data into tableB, then I call insert into tableA select * from tableB where mydate = 20181119. the first stage cost about 40 minutes, the second stage about one hour.
I want to know if have a method faster. Thanks a lot!
mysql indexing
asked Nov 19 at 11:31
Z.Lun
3719
I have a partition table with about 50 indexs and I will insert about 20 million
records every day.
Every time I call insert into tableA values (..),(..)... to insert 2000 records.this will take about five hours.
I can't load data from file. So I create a table talbeB as same as tableA, but tableB don't have index. First I insert data into tableB, then I call insert into tableA select * from tableB where mydate = 20181119. the first stage cost about 40 minutes, the second stage about one hour.
I want to know if have a method faster. Thanks a lot!
mysql indexing
mysql indexing
asked Nov 19 at 11:31
Z.Lun
3719
asked Nov 19 at 11:31
Z.Lun
3719
asked Nov 19 at 11:31
Z.Lun
3719
asked Nov 19 at 11:31
Z.Lun
3719
asked Nov 19 at 11:31
Z.Lun
3719
3719
1
50 indexes?Show theSHOW CREATE TABLE tablestatement maybe you can drop some??
– Raymond Nijland
Nov 19 at 11:33
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
1
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35
|
show 7 more comments
1
50 indexes?Show theSHOW CREATE TABLE tablestatement maybe you can drop some??
– Raymond Nijland
Nov 19 at 11:33
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
1
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35
1
1
50 indexes? Show the SHOW CREATE TABLE table statement maybe you can drop some??– Raymond Nijland
Nov 19 at 11:33
50 indexes? Show the SHOW CREATE TABLE table statement maybe you can drop some??– Raymond Nijland
Nov 19 at 11:33
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
1
1
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35
|
show 7 more comments
1 Answer
1
active
oldest
votes
- Don't index flags, or other low-cardinality columns, by themselves. The Optimizer won't use the index.
UNIQUEindexes are more costly than non-unique ones. How many of them do you have.- Batch 100, not 2000 rows at a time in a single
INSERTstatement. - Is Replication involved?
- Is the table InnoDB? (MyISAM has lots of other problems, and a few solutions.)
- How much RAM do you have? What is the value of
innodb_buffer_pool_size? - What do you mean by "first stage"?
PARTITIONby date? Provide the actual partition definition. There are good ways to do it and useless ways.- How many partitions? More than 50 has performance problems. Tell me how many, plus provide
SHOW VARIABLESandSHOW GLOBAL STATUS; there may be a workaround if you have too many. - Will you be purging "old" data? Is that the reason for partitioning? If not, then partitioning may be hurting more than helping.
- Do not use RAM disk; it takes away from better uses of RAM.
- How many rows are to be inserted, and how often? That is, is this an hourly load of 2M rows? Or a continuous load of 250/second? Or some other pattern?
- SSD drives?
- This smells like a Data Warehouse. There are many techniques for such. The main one is to offload stuff from the Fact table into Summary Tables. You can probably get rid of most of the 50 indexes once you do this. Furthermore, building a "report" from a Summary table may be 10 times as fast as direct from the Fact table.
Please provide SHOW CREATE TABLE for further discussion. You can obfuscate the column names, but be consistent with the indexes and have the actual datatypes.
DW: http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables: http://mysql.rjweb.org/doc.php/summarytables
High Speed Ingestion (aimed at 'continuous'): http://mysql.rjweb.org/doc.php/staging_table
Partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
answered Nov 20 at 4:42
Rick James
65.9k55897
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%2f53373734%2fmysqlhow-to-insert-into-a-table-with-many-index-faster%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
- Don't index flags, or other low-cardinality columns, by themselves. The Optimizer won't use the index.
UNIQUEindexes are more costly than non-unique ones. How many of them do you have.- Batch 100, not 2000 rows at a time in a single
INSERTstatement. - Is Replication involved?
- Is the table InnoDB? (MyISAM has lots of other problems, and a few solutions.)
- How much RAM do you have? What is the value of
innodb_buffer_pool_size? - What do you mean by "first stage"?
PARTITIONby date? Provide the actual partition definition. There are good ways to do it and useless ways.- How many partitions? More than 50 has performance problems. Tell me how many, plus provide
SHOW VARIABLESandSHOW GLOBAL STATUS; there may be a workaround if you have too many. - Will you be purging "old" data? Is that the reason for partitioning? If not, then partitioning may be hurting more than helping.
- Do not use RAM disk; it takes away from better uses of RAM.
- How many rows are to be inserted, and how often? That is, is this an hourly load of 2M rows? Or a continuous load of 250/second? Or some other pattern?
- SSD drives?
- This smells like a Data Warehouse. There are many techniques for such. The main one is to offload stuff from the Fact table into Summary Tables. You can probably get rid of most of the 50 indexes once you do this. Furthermore, building a "report" from a Summary table may be 10 times as fast as direct from the Fact table.
Please provide SHOW CREATE TABLE for further discussion. You can obfuscate the column names, but be consistent with the indexes and have the actual datatypes.
DW: http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables: http://mysql.rjweb.org/doc.php/summarytables
High Speed Ingestion (aimed at 'continuous'): http://mysql.rjweb.org/doc.php/staging_table
Partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
answered Nov 20 at 4:42
Rick James
65.9k55897
add a comment |
- Don't index flags, or other low-cardinality columns, by themselves. The Optimizer won't use the index.
UNIQUEindexes are more costly than non-unique ones. How many of them do you have.- Batch 100, not 2000 rows at a time in a single
INSERTstatement. - Is Replication involved?
- Is the table InnoDB? (MyISAM has lots of other problems, and a few solutions.)
- How much RAM do you have? What is the value of
innodb_buffer_pool_size? - What do you mean by "first stage"?
PARTITIONby date? Provide the actual partition definition. There are good ways to do it and useless ways.- How many partitions? More than 50 has performance problems. Tell me how many, plus provide
SHOW VARIABLESandSHOW GLOBAL STATUS; there may be a workaround if you have too many. - Will you be purging "old" data? Is that the reason for partitioning? If not, then partitioning may be hurting more than helping.
- Do not use RAM disk; it takes away from better uses of RAM.
- How many rows are to be inserted, and how often? That is, is this an hourly load of 2M rows? Or a continuous load of 250/second? Or some other pattern?
- SSD drives?
- This smells like a Data Warehouse. There are many techniques for such. The main one is to offload stuff from the Fact table into Summary Tables. You can probably get rid of most of the 50 indexes once you do this. Furthermore, building a "report" from a Summary table may be 10 times as fast as direct from the Fact table.
Please provide SHOW CREATE TABLE for further discussion. You can obfuscate the column names, but be consistent with the indexes and have the actual datatypes.
DW: http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables: http://mysql.rjweb.org/doc.php/summarytables
High Speed Ingestion (aimed at 'continuous'): http://mysql.rjweb.org/doc.php/staging_table
Partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
answered Nov 20 at 4:42
Rick James
65.9k55897
add a comment |
- Don't index flags, or other low-cardinality columns, by themselves. The Optimizer won't use the index.
UNIQUEindexes are more costly than non-unique ones. How many of them do you have.- Batch 100, not 2000 rows at a time in a single
INSERTstatement. - Is Replication involved?
- Is the table InnoDB? (MyISAM has lots of other problems, and a few solutions.)
- How much RAM do you have? What is the value of
innodb_buffer_pool_size? - What do you mean by "first stage"?
PARTITIONby date? Provide the actual partition definition. There are good ways to do it and useless ways.- How many partitions? More than 50 has performance problems. Tell me how many, plus provide
SHOW VARIABLESandSHOW GLOBAL STATUS; there may be a workaround if you have too many. - Will you be purging "old" data? Is that the reason for partitioning? If not, then partitioning may be hurting more than helping.
- Do not use RAM disk; it takes away from better uses of RAM.
- How many rows are to be inserted, and how often? That is, is this an hourly load of 2M rows? Or a continuous load of 250/second? Or some other pattern?
- SSD drives?
- This smells like a Data Warehouse. There are many techniques for such. The main one is to offload stuff from the Fact table into Summary Tables. You can probably get rid of most of the 50 indexes once you do this. Furthermore, building a "report" from a Summary table may be 10 times as fast as direct from the Fact table.
Please provide SHOW CREATE TABLE for further discussion. You can obfuscate the column names, but be consistent with the indexes and have the actual datatypes.
DW: http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables: http://mysql.rjweb.org/doc.php/summarytables
High Speed Ingestion (aimed at 'continuous'): http://mysql.rjweb.org/doc.php/staging_table
Partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
answered Nov 20 at 4:42
Rick James
65.9k55897
- Don't index flags, or other low-cardinality columns, by themselves. The Optimizer won't use the index.
UNIQUEindexes are more costly than non-unique ones. How many of them do you have.- Batch 100, not 2000 rows at a time in a single
INSERTstatement. - Is Replication involved?
- Is the table InnoDB? (MyISAM has lots of other problems, and a few solutions.)
- How much RAM do you have? What is the value of
innodb_buffer_pool_size? - What do you mean by "first stage"?
PARTITIONby date? Provide the actual partition definition. There are good ways to do it and useless ways.- How many partitions? More than 50 has performance problems. Tell me how many, plus provide
SHOW VARIABLESandSHOW GLOBAL STATUS; there may be a workaround if you have too many. - Will you be purging "old" data? Is that the reason for partitioning? If not, then partitioning may be hurting more than helping.
- Do not use RAM disk; it takes away from better uses of RAM.
- How many rows are to be inserted, and how often? That is, is this an hourly load of 2M rows? Or a continuous load of 250/second? Or some other pattern?
- SSD drives?
- This smells like a Data Warehouse. There are many techniques for such. The main one is to offload stuff from the Fact table into Summary Tables. You can probably get rid of most of the 50 indexes once you do this. Furthermore, building a "report" from a Summary table may be 10 times as fast as direct from the Fact table.
Please provide SHOW CREATE TABLE for further discussion. You can obfuscate the column names, but be consistent with the indexes and have the actual datatypes.
DW: http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables: http://mysql.rjweb.org/doc.php/summarytables
High Speed Ingestion (aimed at 'continuous'): http://mysql.rjweb.org/doc.php/staging_table
Partitioning: http://mysql.rjweb.org/doc.php/partitionmaint
answered Nov 20 at 4:42
Rick James
65.9k55897
answered Nov 20 at 4:42
Rick James
65.9k55897
answered Nov 20 at 4:42
Rick James
65.9k55897
answered Nov 20 at 4:42
Rick James
65.9k55897
65.9k55897
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53373734%2fmysqlhow-to-insert-into-a-table-with-many-index-faster%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
50 indexes?Show theSHOW CREATE TABLE tablestatement maybe you can drop some??– Raymond Nijland
Nov 19 at 11:33
Are these in date order? Is tableA's date always less than tableB?
– danblack
Nov 19 at 11:34
@RaymondNijland sorry I can not. it involves business secrets
– Z.Lun
Nov 19 at 11:34
1
Just follow MySQL's tips for bulk loading/inserting: dev.mysql.com/doc/refman/5.5/en/…
– Tim Biegeleisen
Nov 19 at 11:35
Wow your not leaking out business secrets with a CREATE TABLE statement?
– Raymond Nijland
Nov 19 at 11:35