Storage and query efficiency in MySQL and mariadb database?

Multi tool use
I am new to querying in very big databases. I would like to know if partitioning a very large data table in several improves or decreases efficiency in storage and the speed of queries. For example if a I have a very big data table with information worth a year with 300 million rows and nearly 30 columns, is it better to break it into 12 months or keep it asis? will it improve effiency or storage, or none?
mysql mariadb partitioning data-warehouse
add a comment |
I am new to querying in very big databases. I would like to know if partitioning a very large data table in several improves or decreases efficiency in storage and the speed of queries. For example if a I have a very big data table with information worth a year with 300 million rows and nearly 30 columns, is it better to break it into 12 months or keep it asis? will it improve effiency or storage, or none?
mysql mariadb partitioning data-warehouse
Diegolog -- to avoid having this closed as "too broad", describe the application and provideSHOW CREATE TABLE
.
– Rick James
Dec 30 '18 at 1:27
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32
add a comment |
I am new to querying in very big databases. I would like to know if partitioning a very large data table in several improves or decreases efficiency in storage and the speed of queries. For example if a I have a very big data table with information worth a year with 300 million rows and nearly 30 columns, is it better to break it into 12 months or keep it asis? will it improve effiency or storage, or none?
mysql mariadb partitioning data-warehouse
I am new to querying in very big databases. I would like to know if partitioning a very large data table in several improves or decreases efficiency in storage and the speed of queries. For example if a I have a very big data table with information worth a year with 300 million rows and nearly 30 columns, is it better to break it into 12 months or keep it asis? will it improve effiency or storage, or none?
mysql mariadb partitioning data-warehouse
mysql mariadb partitioning data-warehouse
edited Dec 30 '18 at 1:26
Rick James
67.3k55899
67.3k55899
asked Dec 30 '18 at 0:46


DiegologDiegolog
31
31
Diegolog -- to avoid having this closed as "too broad", describe the application and provideSHOW CREATE TABLE
.
– Rick James
Dec 30 '18 at 1:27
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32
add a comment |
Diegolog -- to avoid having this closed as "too broad", describe the application and provideSHOW CREATE TABLE
.
– Rick James
Dec 30 '18 at 1:27
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32
Diegolog -- to avoid having this closed as "too broad", describe the application and provide
SHOW CREATE TABLE
.– Rick James
Dec 30 '18 at 1:27
Diegolog -- to avoid having this closed as "too broad", describe the application and provide
SHOW CREATE TABLE
.– Rick James
Dec 30 '18 at 1:27
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32
add a comment |
1 Answer
1
active
oldest
votes
Short answer: No. Disk space will be slightly larger; performance is not likely to improve.
Long answer:
Each partition is essentially a separate table; each table has some pre-allocated space. A partition has 4-7MB of "free" space each.
Partitioning does not inherently bring any performance boost. (In some cases, it give a slight slowdown.)
That said, there are 4 use cases that I know of where partitioning, if carefully done, can provide a noticeable speed up. You have said nothing to indicate yours is one of those cases. The most common is a "time series", wherein you need to delete "old" data. The benefit comes from DROP PARTITION
being virtually instantaneous, as opposed to DELETE
.
Please describe your data situation -- the data type, the schema, and the important queries. If it is a Data Warehouse application, then "time series" may be of use. More likely is Summary tables; they can often be used to greatly speed up "Reports", as opposed to lengthy GROUP BYs
.
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
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%2f53974471%2fstorage-and-query-efficiency-in-mysql-and-mariadb-database%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
Short answer: No. Disk space will be slightly larger; performance is not likely to improve.
Long answer:
Each partition is essentially a separate table; each table has some pre-allocated space. A partition has 4-7MB of "free" space each.
Partitioning does not inherently bring any performance boost. (In some cases, it give a slight slowdown.)
That said, there are 4 use cases that I know of where partitioning, if carefully done, can provide a noticeable speed up. You have said nothing to indicate yours is one of those cases. The most common is a "time series", wherein you need to delete "old" data. The benefit comes from DROP PARTITION
being virtually instantaneous, as opposed to DELETE
.
Please describe your data situation -- the data type, the schema, and the important queries. If it is a Data Warehouse application, then "time series" may be of use. More likely is Summary tables; they can often be used to greatly speed up "Reports", as opposed to lengthy GROUP BYs
.
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
add a comment |
Short answer: No. Disk space will be slightly larger; performance is not likely to improve.
Long answer:
Each partition is essentially a separate table; each table has some pre-allocated space. A partition has 4-7MB of "free" space each.
Partitioning does not inherently bring any performance boost. (In some cases, it give a slight slowdown.)
That said, there are 4 use cases that I know of where partitioning, if carefully done, can provide a noticeable speed up. You have said nothing to indicate yours is one of those cases. The most common is a "time series", wherein you need to delete "old" data. The benefit comes from DROP PARTITION
being virtually instantaneous, as opposed to DELETE
.
Please describe your data situation -- the data type, the schema, and the important queries. If it is a Data Warehouse application, then "time series" may be of use. More likely is Summary tables; they can often be used to greatly speed up "Reports", as opposed to lengthy GROUP BYs
.
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
add a comment |
Short answer: No. Disk space will be slightly larger; performance is not likely to improve.
Long answer:
Each partition is essentially a separate table; each table has some pre-allocated space. A partition has 4-7MB of "free" space each.
Partitioning does not inherently bring any performance boost. (In some cases, it give a slight slowdown.)
That said, there are 4 use cases that I know of where partitioning, if carefully done, can provide a noticeable speed up. You have said nothing to indicate yours is one of those cases. The most common is a "time series", wherein you need to delete "old" data. The benefit comes from DROP PARTITION
being virtually instantaneous, as opposed to DELETE
.
Please describe your data situation -- the data type, the schema, and the important queries. If it is a Data Warehouse application, then "time series" may be of use. More likely is Summary tables; they can often be used to greatly speed up "Reports", as opposed to lengthy GROUP BYs
.
Short answer: No. Disk space will be slightly larger; performance is not likely to improve.
Long answer:
Each partition is essentially a separate table; each table has some pre-allocated space. A partition has 4-7MB of "free" space each.
Partitioning does not inherently bring any performance boost. (In some cases, it give a slight slowdown.)
That said, there are 4 use cases that I know of where partitioning, if carefully done, can provide a noticeable speed up. You have said nothing to indicate yours is one of those cases. The most common is a "time series", wherein you need to delete "old" data. The benefit comes from DROP PARTITION
being virtually instantaneous, as opposed to DELETE
.
Please describe your data situation -- the data type, the schema, and the important queries. If it is a Data Warehouse application, then "time series" may be of use. More likely is Summary tables; they can often be used to greatly speed up "Reports", as opposed to lengthy GROUP BYs
.
answered Dec 30 '18 at 1:24
Rick JamesRick James
67.3k55899
67.3k55899
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
add a comment |
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
Rick. Thank you very much for your answer. The database is a shipments over time data. meaning I have a lot of data on shipments scans showing where they were recieved and where the were sent. this is the biggest data table. There is some shimpment related information that is smaller in size in another data table somewhere around the 100 million rows. On top of that I have some information related to each one of the items related in relatively tiny data tables that dont go over the million rows. I am trying to get the number of shipments on different locations and given certain features
– Diegolog
Dec 30 '18 at 1:38
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
@Diegolog - You're welcome. I'd be glad to help further, but things are still rather vague.
– Rick James
Dec 30 '18 at 1:59
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%2f53974471%2fstorage-and-query-efficiency-in-mysql-and-mariadb-database%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
bJGTR0L6,RPjZFS QP1d HIGQ 8HLyQUhakeX3Q,BCz,7btCGx b40,2BJ
Diegolog -- to avoid having this closed as "too broad", describe the application and provide
SHOW CREATE TABLE
.– Rick James
Dec 30 '18 at 1:27
Rick thanks for the suggestion. I will try to add some example code.
– Diegolog
Dec 30 '18 at 1:32