MySQL Partition over the Virtual / Generated Column

                Had an interesting requirement from one of our client to have the two MySQL partitions ( partition_full / partition_half ) for store the Names based on the user input.

Requirement :

  • The table have two columns first_name and last_name. With the user input of both columns, it needs to be automatically compute the data for another column full_name . And, the status needs be consider as “FULL” .
  • If the column last_name don’t have the input from the user, then the first_name data needs to be compute as the full_name . And, the status needs be considered as “HALF” .
  • Need the separate partitions for both status HALF and FULL

We can achieve this with the help of Virtual / Generated columns and LIST partition . In this blog, I am going to explain the complete steps which I followed to achieve this .

What is Virtual Column ?

                Virtual columns are the generated columns because the data set for these columns will be computed based on the predefined column structure . Below are the three types we can generate the virtual columns .

  • STORED
  • VIRTUAL
  • GENERATED ALWAYS

Here is the detailed blog post from Mydbops Team, which contains the nice details about Virtual columns .

MySQL partition with Virtual / Generated columns

Step 1  –  ( Creating the table with virtual columns )

cmd : create table Virtual_partition_test (id int(11) not null auto_increment primary key, first_name varchar(16), last_name varchar(16) default 0, full_name varchar(32) as (case last_name when ‘0’ then first_name else concat(first_name,’ ‘,last_name) end) stored, name_stat varchar(7) as (case full_name when concat(first_name,’ ‘,last_name) then ‘full’ else ‘half’ end) stored, email_id varchar(16));

  • full_name  – for compute the data set from columns firstname and lastname .
  • name_stat – for compute the name status from columns firstname and lastname .

Step 2 –  ( Testing the virtual/generated column behaviour ) 

cmd :

insert into Virtual_partition_test (first_name,last_name,email_id) values (‘sri’,’ram’,’sriram@gmail.com’),(‘hercules’,’7sakthi’,’hercules7sakthi@gmail.com’),(‘asha’,’mary’,’ashamary@gmail.com’);

insert into Virtual_partition_test (first_name,email_id) values (‘vijaya’,’vijaya@gmail.com’),(‘durai’,’durai@gmail.com’),(‘jc’,’jc@gmail.com’);

Yes, I have created 3 FULL and 3 HALF names .

The above result set, illustrates that the virtual/generated column is working perfectly as expected .

Step 3 –  ( Adding the partition key  )

It is important to have the partition column as the part of PRIMARY KEY .

cmd : alter table Virtual_partition_test drop primary key, add primary key (id,name_stat);

Step 4 – ( Configuring the partition )

cmd :

alter table Virtual_partition_test partition by list
columns(name_stat)
(partition partition_full values in (‘FULL’) engine=InnoDB,
partition partition_half values in (‘HALF’) engine=InnoDB);

Partitions has been added as per the requirement .

more informations,

Hope this blog will help someone who is looking the partitions over the virtual / generated columns .

Thanks !!!

5 thoughts on “MySQL Partition over the Virtual / Generated Column

  1. Well documented.
    Just wanted understand, What use-case could be to create partition on column with only two distinct values and very low cardinality?!

    Liked by 1 person

    1. They have the future plan to archive a partition ( half ) data to another place and the end users will not be allowed to write the table without last_name .

      As per the estimation, that time the table size will be around 1 TB . So, without partition we need to rebuilt the table for recover the space once archival completed . It is painful to rebuilt a TB table at hot system . With partition we can simply drop the partition ( after archival ) and recover the space at the point .

      Also, with the partition we can expect the better query performance as the table was internally divided twice . From the blog last screenshot , you can see more details .

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: