Partitioning

Partitioning :: Partitioning allows you to store parts of your table in their own logical space. With partitioning, you want to divide up your rows based on how you access them. If you partition your rows and you are still hitting all the partitions, it does you no good. The goal is that when you query, you will only have to look at a subset of the data to get a result, and not the whole table.
   * A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance reasons.
  
   * RANGE : RANGE partitioning contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. Rows are partitioned based on the range of a column (i.e date, 2006-2007, 2007-20008, etc,.). Range partitioning is particularly useful when you want or need to delete “old” data. Can simply use ALTER TABLE employees DROP PARTITION p0.
   Example: PARTITION BY RANGE ( YEAR(hired) ) (
            PARTITION p0 VALUES LESS THAN (1991)
            PARTITION p3 VALUES LESS THAN MAXVALUE
            );
           
   * LIST : List partitioning in MySQL is similar to range partitioning in many ways. As in partitioning by RANGE, each partition must be explicitly defined. The chief difference between the two types of partitioning is that, in list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists, rather than in one of a set of contiguous ranges of values. This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. Unlike the case with partitions defined by range, list partitions do not need to be declared in any particular order.
   Example: PARTITION BY LIST(dept_id) (
            PARTITION pB1 VALUES IN (3,5,6,9,17),
            PARTITION pB2 VALUES IN (1,2,10,11,19,20));
           
   * HASH : Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.
   Example: PARTITION BY HASH(dept_id)
            PARTITIONS 4;
           
   * Key : Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server. MySQL Cluster uses MD5() for this purpose.
   Example: PARTITION BY KEY()
            PARTITIONS 2;