Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL 5.5 partition table by A-Z

I understand that as of MySQL 5.5, you can now partition a table by non-integer values like a varchar. I have a table where I perform a lot of lookups on a single varchar column, hence I would like to partition on that for performance reasons.

In all instances, the value of the column is a single alphabetical word (strictly lower case a-z, enforced by validation).

What I would like to do is partition this table by the first letter in each word stored, so all words beginning with 'a' go in the first partition, 'b' in the second etc.

My gut feeling is that I could probably construct the create/alter table statement to use the LIKE statement, but am unsure about the syntax.

Has anyone done anything like this using MySQL 5.5?

like image 677
alphadevx Avatar asked Mar 23 '11 15:03

alphadevx


People also ask

Does MySQL 5.7 support partition by?

MySQL 5.7 supports explicit selection of partitions and subpartitions that, when executing a statement, should be checked for rows matching a given WHERE condition.

How do I partition a table in MySQL?

We can create a partition in MySQL using the CREATE TABLE or ALTER TABLE statement. Below is the syntax of creating partition using CREATE TABLE command: CREATE TABLE [IF NOT EXISTS] table_name. (column_definitions)

Does MySQL support partition by?

For information about window functions, see Section 12.21, “Window Functions”. In MySQL 8.0, partitioning support is provided by the InnoDB and NDB storage engines.

Can you partition an existing table MySQL?

This table can be partitioned by HASH , using the id column as the partitioning key, into 8 partitions by means of this statement: ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8; MySQL supports an ALGORITHM option with [SUB]PARTITION BY [LINEAR] KEY .


1 Answers

If you are determined to do it by the first letter, I think that RANGE partitioning would do the trick. However, if you don't have an absolute requirement for the partitioning to by by first letter, LINEAR KEY partitioning might be better.

Here is an example I lifted from the manual page sited and modified to use a varchar column:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS(fname) (
    PARTITION p0 VALUES LESS THAN ('h'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

And running it:

... Physical database connection acquired for: Feynman
 12:33:07  [CREATE - 0 row(s), 0.062 secs]  Command processed. No rows were affected
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.062/0.000 sec  [0 successful, 1 warnings, 0 errors]
like image 85
Tom Micheline Avatar answered Sep 27 '22 18:09

Tom Micheline