1
       | columnName |    
       --------------    
            201
            202
            210
            211

I have 5 million records and I want to partition the table by the substring of it's column.

For example, I want all 20s, 21s, 22s, and so on to be on the same partition.

SUBSTRING(columnName, 1, 2)

  • Please report back on whether the partitioning helped any. – Rick James Aug 15 '17 at 1:32
2

If the columnName is an INT, you can partition it as follows

CREATE TABLE mydata
(
    id INT NOT NULL AUTO_INCREMENT,
    myColumn INT NOT NULL,
    PRIMARY KEY (id,myColumn),
    KEY (myColumn)
)
PARTITION BY RANGE (myColumn) (
    PARTITION p000 VALUES LESS THAN (010),
    PARTITION p010 VALUES LESS THAN (020),
    PARTITION p020 VALUES LESS THAN (030),
    PARTITION p030 VALUES LESS THAN (040),
    PARTITION p040 VALUES LESS THAN (050),
    PARTITION p050 VALUES LESS THAN (060),
    PARTITION p060 VALUES LESS THAN (070),
    PARTITION p070 VALUES LESS THAN (080),
    PARTITION p080 VALUES LESS THAN (090),
    PARTITION p090 VALUES LESS THAN (100),
    PARTITION p100 VALUES LESS THAN (110),
    PARTITION p110 VALUES LESS THAN (120),
    PARTITION p120 VALUES LESS THAN (130),
    PARTITION p130 VALUES LESS THAN (140),
    PARTITION p140 VALUES LESS THAN (150),
    PARTITION p150 VALUES LESS THAN (160),
    PARTITION p160 VALUES LESS THAN (170),
    PARTITION p170 VALUES LESS THAN (180),
    PARTITION p180 VALUES LESS THAN (190),
    PARTITION p190 VALUES LESS THAN (200),
    PARTITION p200 VALUES LESS THAN (210),
    PARTITION p210 VALUES LESS THAN (220),
    PARTITION p220 VALUES LESS THAN (230),
    PARTITION p230 VALUES LESS THAN (240),
    PARTITION p240 VALUES LESS THAN (250),
    PARTITION p250 VALUES LESS THAN (260),
    PARTITION p260 VALUES LESS THAN (270),
    PARTITION p270 VALUES LESS THAN (280),
    PARTITION p280 VALUES LESS THAN (290),
    PARTITION p290 VALUES LESS THAN (300),
    PARTITION p300 VALUES LESS THAN (310),
    PARTITION p310 VALUES LESS THAN (320),
    PARTITION p320 VALUES LESS THAN (330),
    PARTITION p330 VALUES LESS THAN (340),
    PARTITION p340 VALUES LESS THAN (350),
    PARTITION p350 VALUES LESS THAN (360),
    PARTITION p360 VALUES LESS THAN (370),
    PARTITION p370 VALUES LESS THAN (380),
    PARTITION p380 VALUES LESS THAN (390),
    PARTITION p390 VALUES LESS THAN (400),
    PARTITION p400 VALUES LESS THAN (410),
    PARTITION p410 VALUES LESS THAN (420),
    PARTITION p420 VALUES LESS THAN (430),
    PARTITION p430 VALUES LESS THAN (440),
    PARTITION p440 VALUES LESS THAN (450),
    PARTITION p450 VALUES LESS THAN (460),
    PARTITION p460 VALUES LESS THAN (470),
    PARTITION p470 VALUES LESS THAN (480),
    PARTITION p480 VALUES LESS THAN (490),
    PARTITION p490 VALUES LESS THAN (500),
    PARTITION p500 VALUES LESS THAN (510),
    PARTITION p510 VALUES LESS THAN (520),
    PARTITION p520 VALUES LESS THAN (530),
    PARTITION p530 VALUES LESS THAN (540),
    PARTITION p540 VALUES LESS THAN (550),
    PARTITION p550 VALUES LESS THAN (560),
    PARTITION p560 VALUES LESS THAN (570),
    PARTITION p570 VALUES LESS THAN (580),
    PARTITION p580 VALUES LESS THAN (590),
    PARTITION p590 VALUES LESS THAN (600),
    PARTITION p600 VALUES LESS THAN (610),
    PARTITION p610 VALUES LESS THAN (620),
    PARTITION p620 VALUES LESS THAN (630),
    PARTITION p630 VALUES LESS THAN (640),
    PARTITION p640 VALUES LESS THAN (650),
    PARTITION p650 VALUES LESS THAN (660),
    PARTITION p660 VALUES LESS THAN (670),
    PARTITION p670 VALUES LESS THAN (680),
    PARTITION p680 VALUES LESS THAN (690),
    PARTITION p690 VALUES LESS THAN (700),
    PARTITION p700 VALUES LESS THAN (710),
    PARTITION p710 VALUES LESS THAN (720),
    PARTITION p720 VALUES LESS THAN (730),
    PARTITION p730 VALUES LESS THAN (740),
    PARTITION p740 VALUES LESS THAN (750),
    PARTITION p750 VALUES LESS THAN (760),
    PARTITION p760 VALUES LESS THAN (770),
    PARTITION p770 VALUES LESS THAN (780),
    PARTITION p780 VALUES LESS THAN (790),
    PARTITION p790 VALUES LESS THAN (800),
    PARTITION p800 VALUES LESS THAN (810),
    PARTITION p810 VALUES LESS THAN (820),
    PARTITION p820 VALUES LESS THAN (830),
    PARTITION p830 VALUES LESS THAN (840),
    PARTITION p840 VALUES LESS THAN (850),
    PARTITION p850 VALUES LESS THAN (860),
    PARTITION p860 VALUES LESS THAN (870),
    PARTITION p870 VALUES LESS THAN (880),
    PARTITION p880 VALUES LESS THAN (890),
    PARTITION p890 VALUES LESS THAN (900),
    PARTITION p900 VALUES LESS THAN (910),
    PARTITION p910 VALUES LESS THAN (920),
    PARTITION p920 VALUES LESS THAN (930),
    PARTITION p930 VALUES LESS THAN (940),
    PARTITION p940 VALUES LESS THAN (950),
    PARTITION p950 VALUES LESS THAN (960),
    PARTITION p960 VALUES LESS THAN (970),
    PARTITION p970 VALUES LESS THAN (980),
    PARTITION p980 VALUES LESS THAN (990),
    PARTITION p990 VALUES LESS THAN MAXVALUE
);

Why partition it this way ??? If it is an INT, here is how the data is grouped

  • Partition p200 will hold rows with myColumn 200 - 209
  • Partition p210 will hold rows with myColumn 210 - 219
  • Partition p220 will hold rows with myColumn 220 - 229

Partitioning works with DATEs and INTs, not CHARs or VARCHARs

|improve this answer|||||
  • my 'columnName' is CHAR(10) type because there is leading zeros to satisfy the character length of 10. – Jam Ville Aug 6 '17 at 3:14
  • I've made it using PARTITION BY RANGE COLUMNS because the column type is CHAR – Jam Ville Aug 6 '17 at 3:17
  • What was the syntax ? Please post it – RolandoMySQLDBA Aug 6 '17 at 3:18
  • ALTER TABLE myTable_6 PARTITION BY RANGE COLUMNS (columnName) PARTITIONS 10 ( PARTITION p0 VALUES LESS THAN ('6100000000'), PARTITION p1 VALUES LESS THAN ('6200000000'), PARTITION p2 VALUES LESS THAN ('6300000000'), PARTITION p3 VALUES LESS THAN ('6400000000'), PARTITION p4 VALUES LESS THAN ('6500000000'), PARTITION p5 VALUES LESS THAN ('6600000000'), PARTITION p6 VALUES LESS THAN ('6700000000'), PARTITION p7 VALUES LESS THAN ('6800000000'), PARTITION p8 VALUES LESS THAN ('6900000000'), PARTITION p9 VALUES LESS THAN MAXVALUE) – Jam Ville Aug 6 '17 at 7:22
  • There is a quote (') in VALUES because they were STRING/CHAR not NUMBER – Jam Ville Aug 6 '17 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.