Search code examples
mysqlmariadbpartitioningdata-warehouse

Storage and query efficiency in MySQL and mariadb database?


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?


Solution

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