MySQL partitioning is a concept with 2 contexts primarily the horizontal partitioning and the other being vertical partitioning.
Partitioning of relational data, it usually refers to decomposing your tables or breaking your tables either row-wise (horizontally) or column-wise (vertically).
Vertical partitioning, aka row splitting, uses the same splitting techniques as database normalization, but usually the term (vertical / horizontal) data partitioning refers to a physical optimization whereas normalization is an optimization on the conceptual level.
This is usually preferred when there is rare data used in one table, and you move the rare data into another table, thus reducing the overall size when running queries.
“Horizontal partitioning“, or sharding, is replicating [copying] the schema, and then dividing the data based on a shard key.
“Vertical partitioning” involves dividing up the schema (and the data goes along for the ride).
Horizontal Partitioning in data base
Keeping all the fields Ex:Table Employees has id,name,Geographical location ,email,designation,phone
Ex:1.Keeping all the fields and distributing records in multiple machines.say id= 1-100000 or 100000-200000 records in one machine each and distributing over multiple machines.
Ex:2.Keeping separate databases for Regions EG: Asia Pacific,North America
Key:Picking set of rows based on a criteria
Vertical Partitioning in data base
It is similar to Normalization where the same table is divided in to multiple tables and used with joins if required.
Ex: id, name,designation is put in one table and phone , email which may not be frequently accessed are put in another.
Key:Picking set of columns based on a criteria.
- Horizontal/Vertical Scaling is different from partitioning
is about adding more machines to enable improved responsiveness and availability of any system including database.The idea is to distribute the work load to multiple machines.
is about adding more capability in the form of CPU,Memory to existing machine or machines to enable improved responsiveness and availability of any system including database.In a virtual machine set up it can be configured virtually instead of adding real physical machines.