CODEWITHSUNDEEP
mysqlsqlrangepartitioning
AngryPanda
AngryPanda

Reputation: 1281

MYSQL 5.5 partition table by first character 0-9a-zA-Z

I want to create a range partition on the first character in the character field. The field has hashtag values and is case sensitive 0-9a-zA-Z

I referred to the solution listed here.

This is what I execute:

CREATE TABLE tweetdbq4(hashtag CHAR(50), timestamp CHAR(14), tweetid BIGINT(18) UNSIGNED, userid INT(10) UNSIGNED) ENGINE=MYISAM 
PARTITION BY RANGE COLUMNS(hashtag) (
    PARTITION p0 VALUES LESS THAN ('a'),
    PARTITION p1 VALUES LESS THAN ('b'),
    PARTITION p2 VALUES LESS THAN ('c'),
    PARTITION p3 VALUES LESS THAN ('d'),
    PARTITION p4 VALUES LESS THAN ('e'),
    PARTITION p5 VALUES LESS THAN ('f'),
    PARTITION p6 VALUES LESS THAN ('g'),
    PARTITION p7 VALUES LESS THAN ('h'),
    PARTITION p8 VALUES LESS THAN ('i'),
    PARTITION p9 VALUES LESS THAN ('j'),
    PARTITION p10 VALUES LESS THAN ('k'),
    PARTITION p11 VALUES LESS THAN ('l'),
    PARTITION p12 VALUES LESS THAN ('m'),
    PARTITION p13 VALUES LESS THAN ('n'),
    PARTITION p14 VALUES LESS THAN ('o'),
    PARTITION p15 VALUES LESS THAN ('p'),
    PARTITION p16 VALUES LESS THAN ('q'),
    PARTITION p17 VALUES LESS THAN ('r'),
    PARTITION p18 VALUES LESS THAN ('s'),
    PARTITION p19 VALUES LESS THAN ('t'),
    PARTITION p20 VALUES LESS THAN ('u'),
    PARTITION p21 VALUES LESS THAN ('v'),
    PARTITION p22 VALUES LESS THAN ('w'),
    PARTITION p23 VALUES LESS THAN ('x'),
    PARTITION p24 VALUES LESS THAN ('y'),
    PARTITION p25 VALUES LESS THAN ('z'),
    PARTITION p26 VALUES LESS THAN ('A'),
    PARTITION p27 VALUES LESS THAN ('B'),
    PARTITION p28 VALUES LESS THAN ('C'),
    PARTITION p29 VALUES LESS THAN ('D'),
    PARTITION p30 VALUES LESS THAN ('E'),
    PARTITION p31 VALUES LESS THAN ('F'),
    PARTITION p32 VALUES LESS THAN ('G'),
    PARTITION p33 VALUES LESS THAN ('H'),
    PARTITION p34 VALUES LESS THAN ('I'),
    PARTITION p35 VALUES LESS THAN ('J'),
    PARTITION p36 VALUES LESS THAN ('K'),
    PARTITION p37 VALUES LESS THAN ('L'),
    PARTITION p38 VALUES LESS THAN ('M'),
    PARTITION p39 VALUES LESS THAN ('N'),
    PARTITION p40 VALUES LESS THAN ('O'),
    PARTITION p41 VALUES LESS THAN ('P'),
    PARTITION p42 VALUES LESS THAN ('Q'),
    PARTITION p43 VALUES LESS THAN ('R'),
    PARTITION p44 VALUES LESS THAN ('S'),
    PARTITION p45 VALUES LESS THAN ('T'),
    PARTITION p46 VALUES LESS THAN ('U'),
    PARTITION p47 VALUES LESS THAN ('V'),
    PARTITION p48 VALUES LESS THAN ('W'),
    PARTITION p49 VALUES LESS THAN ('X'),
    PARTITION p50 VALUES LESS THAN ('Y'),
    PARTITION p51 VALUES LESS THAN ('Z'),
    PARTITION p52 VALUES LESS THAN MAXVALUE
);

I get this error

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

By doing PARTITION p0 VALUES LESS THAN ('a') will it store partition on values starting from 0-9?

Can you tell me what am I doing wrong? Thanks!

Upvotes: 2

Views: 1150

Answers (2)

Rick James
Rick James

Reputation: 142453

Plan A: Use a COLLATION for hashtag that ends in _bin, not _ci. "ci" stands for case insensitive. That is 'a' and 'A' are treated equal.

Plan B: Use only 26+1 partitions, not 52+1.

Why are you PARTITIONing? For performance? You probably won't get any performance boost. Let's see a query that you think will be sped up; I will explain why PARTITIONs will or (more likely) won't help.

Unrelated: Why is timestamp a CHAR(14) instead of a TIMESTAMP? CHAR(14) takes 14 or 42 bytes; timestamp takes 4 or 5. Smaller disk footprint leads to better performance.

Unrelated: Are hashtags exactly 50 characters? If not, use VARCHAR, not CHAR. If you argue that "FIXED" is better, I will explain that that is an old wives tale.

Unrelated: Do SHOW CREATE TABLE to see what CHARACTER SET you have. Then think about whether hashtags should be utf8 or not.

Upvotes: 1

mim.
mim.

Reputation: 677

It's because you're using range for partitioning, you have upper and lower case letters. You need to remove one, cause can not use same characters for range.

Upvotes: 1

Related Questions