Partitioning and Shard

When tables goes larger and there are many records and rarely access to whole tables, just few records. Then, separate table's records into small piece will save the performance when process queries.

There are two approaches to achieve: Partitioning / shard table

Partitioning

Partitioning a table means you cut your table's records into smaller chunk base on value of field( or some fields) of table.

The result is : your table's records' amount reduced. Your query will process faster.

You still can access to every chunks to collect all data of table.

How to do :

• Use PARTITION statement to create partition when create table
• Add partition after create table : ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
• Delete partition : ALTER TABLE t1 DROP PARTITION p0, p1;
• Select with partition: SELECT * FROM employees PARTITION (p0, p2)

Shard

The idea of shard a table is : store data in separated table with same structure. There is logic to identify which record will be located in which table.

The result is: your total records is broken down into several tables.

How to do:

• Create table, shard table with same structure
• Define the fields (shard fields) that their value will indicate the row will belong to table or shard table.
• When select /update/delete must be give the shard fields to get the right table.