I have a very similar problem asked in Partitioning table based on first letter of a varchar field for Postgres, however, I want to do this in mysql 5.7.
Is it possible to partition a table in MySQL based on the first character of a varchar field?
I have a vertical table with the format:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`field_name` varchar(255) NOT NULL,
`field_value` text NOT NULL,
PRIMARY KEY (`id`)
)
The size of this table is more than 100M records.
Most of the select queries we use, specify the field_name to search for. What I want to do is divide the table based on field_name.
So that when we have a query like field_name='email' and field_value='xyz@mail.com' then the query simply searches in the partition that contains all the emails and skip rest of the partitions.
The field_name is not a comprehensive list, hence, I won't be able to use list partitioning. Key partitioning also might be tough to use, because I don't want to include the id in the partitioning and key partitioning requires primary key to be included.
For example:
For the following 3 records:
a-blah
a-blah2
b-blah
a-blah and a-blah2 would go in the A partition, b-blah would go into the B partition.
(2) If the above is not possible with MySQL, what is a good way to evenly partition a large growing vertical table?
field_name='email' and field_value='xyz@mail.com'
So, your real problem is the EAV schema design. It has lots of inefficiencies, and Partitioning, especially the way you suggest will not help performance.
WordPress is a common example of EAV, but it can be helped a little by improving the indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
More generically: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
Techniques to help with EAV: http://mysql.rjweb.org/doc.php/eav
There are only 4 cases where Partitioning helps performance; it does not sound like your case fits: http://mysql.rjweb.org/doc.php/partitionmaint