MySql DB Horizontal Table Partition

Posted By : Anil Kumar | 21-Apr-2018

mysql

Introduction:

In today's world, there is two types of issues are faced in a project

(i) Memory Management

(ii) Time Management

As the technologies are growing up there is a less priority of memory management the focus is now on time management.

Projects are going complex as per the needs of the world to automate everything.

In a database data is well formed in tables.

When we are inserting data into a table its take less time to do that task.

But when we are getting data from database then its depends on two constraints.

(i) How many fields in where clause

(ii) Number of rows in the table

We know that the number of rows is ever growing in a live project.

So as per the records are increased the time management efficiency is decreased.

When the data is increased over a limit we migrate that data.

So here is the proposed solution for time management.

We can use table partition.

A partition can make as per the requirement.

We try to make a partition as the minimal where clause is required to get the data.

We can also insert data directly to its partition

Here is the example of same

1. CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) 
PARTITION BY LIST COLUMNS (name) 
(PARTITION p0 VALUES IN ('ETH/USD'), 
PARTITION p1 VALUES IN('BTC/USD'), 
PARTITION p2 VALUES IN('BTC/KES'), 
PARTITION p3 VALUES IN('ETH/KES'));

2.select PARTITION_NAME from information_schema.partitions;

INSERT INTO trb3 VALUES
 ('1', 'ETH/USD', NULL), 
('2', 'BTC/USD', NULL),
('3', 'BTC/KES', NULL), 
('4', 'BTC/KES', NULL), 
('5', 'ETH/KES', NULL),  
('6', 'ETH/KES', NULL), 
('7', 'BTC/USD', NULL), 
('8', 'BTC/USD', NULL), 
('9', 'ETH/USD', NULL), 
('10', 'ETH/USD', NULL) ;



3. select * from trb3 PARTITION(p0);
4. select * from trb3 PARTITION(p3);
5. INSERT INTO trb3 PARTITION(p3) VALUES ('11', 'ETH/KES', NULL);