Storage and query efficiency in MySQL and mariadb database?

Multi tool use
Multi tool use












0















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?










share|improve this question

























  • 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
















0















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?










share|improve this question

























  • 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














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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



















  • 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

















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












1 Answer
1






active

oldest

votes


















2














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.






share|improve this answer
























  • 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













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
});


}
});














draft saved

draft discarded


















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









2














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.






share|improve this answer
























  • 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


















2














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.






share|improve this answer
























  • 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
















2












2








2







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










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





















  • 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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
QhVwx kEhOmc5CJ6Zfx9rC ngDWtf aspPSAOTClHXu2okFONJ vIPb4vRRIFdWlHSp,8CvnDRUp4 9kxNf

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas