Database Systems with SQL - Full Course - Database Systems - Partitions - Horizontal vs Vertical Partition and Shards

A partition is a small piece, or subset, of database table. A database table can have lots of partitions, which don’t overlap, and make up all the table data There are two types of partitions A horizontal partition is a subset of table rows. A vertical partition is a subset of table columns. Most relational databases such as MySQL are horizontal. Partitions are automatically stored in separate tablespaces by the database, but DBAs can also specify where to put them. Table indexes are also partitioned, where the indexes in each partition only contain the rows for that partition. A shard is a subset of a database table, but the data is spread across multiple computers, or a distributed databases. With partitioning, the data stays in a single database instance. A partition expression specifies the value of a single column or multiple partition columns. Partition rows are assigned in several different ways: Range partition – each partition is associated with a range of partition expression values. To set the range, specify the upper bound with keywords VALUES LESS THAN, and specify the high column value with MAXVALUE. Range partition types are the most commonly used. List partition – each partition is associated with a list of partition expression values. This is done using the keywords VALUES IN. Hash partition – partition expressions must a positive integer value, specified by the DBA, and are numbered starting with 0 to N-1. Since it’s a hash, each row’s partition is computed as the partition expression value % N. (% is modulo, which gets the integer remainder). In MySQL, A key partition is like a hash partition, but the partition expression is automatically determined by the database. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!