MySQL, the most popular open source SQL database management system, provides high performance, flexibility, manageability, scalability, availability, strong data protection and ease of use. If you leave all data in one table, it will take more time to retrieve your data and reduces your database performance. In order to achieve high performance and reduce response time , you can use “Database Partitioning” technique.
What is Database Partitioning?
Partitioning is a database design technique which enables user to enhance the database performance and also simplifies the overall data management and operations. This technique splits large database into smaller and more manageable ones.
Generally, partitioning allows you to store parts of your table in their own logical space. MySQL supports horizontal partitioning which allowing the rows of a database to be divided into smaller subset. Partitioning increases query performance, which means when you query, you will only have to look at a subset of the data to get a result rather than whole table. The idea behind partitioning is not to use multiple servers but to use multiple tables instead of using single table.
What are the types of partitioning techniques?
There are various ways in MySQL to partition a database, such as:
Range Partitioning:
In this method, tables are partitioned based on the column values falling within a given range. This type of partitioning is useful when you are frequently running queries that depend directly on the column which is used for partitioning the table. It is important to note that each partition is defined in order from lowest to highest.
List Partitioning:
List partitioning is very analogous to range partitioning. As in Range partitioning, each partition range must be explicitly defined. The main difference between the two types of the partitioning is that, in list partitioning, each partition is segmented and selected based on the column matching a set of predefined lists, rather than one of a set of contiguous range of values.
Hash Partitioning:
Hash partitioning ensures an even distribution of data across predefined number of partitions. In this partitioning, MySQL automatically takes care of which partition a given column value is to be stored, rather than specifying the range or list value explicitly. This type of partitioning is useful when ranges are not appropriate.
KEY Partitioning:
Key partitioning is almost similar to Hash partitioning, conceptually and syntactically, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.
This technique reduces overall response time by reducing the amount of data read operation for a typical SQL operation and achieves a greater degree of query throughput by spreading data more logically.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
