Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:

ALTER TABLE `test`.`test`
PARTITION BY RANGE COLUMNS (city, state) (
    PARTITION p0 VALUES LESS THAN ('a','AE'),
    PARTITION p1 VALUES LESS THAN ('c','AE'),
    PARTITION p2 VALUES LESS THAN ('e','AE'),
    PARTITION p3 VALUES LESS THAN ('g','AE'),
    PARTITION p4 VALUES LESS THAN ('i','AE'),
    PARTITION p5 VALUES LESS THAN ('k','AE'),
    PARTITION p6 VALUES LESS THAN ('m','AE'),
    PARTITION p7 VALUES LESS THAN ('o','AE'),
    PARTITION p8 VALUES LESS THAN ('q','AE'),
    PARTITION p9 VALUES LESS THAN ('s','AE'),
    PARTITION p10 VALUES LESS THAN ('u','AE'),
    PARTITION p11 VALUES LESS THAN ('w','AE'),
    PARTITION p12 VALUES LESS THAN ('y','AE'),
    PARTITION p13 VALUES LESS THAN ('a','AK'),
    PARTITION p14 VALUES LESS THAN ('c','AK'),
    PARTITION p15 VALUES LESS THAN ('e','AK'),
    PARTITION p16 VALUES LESS THAN ('g','AK'),
    PARTITION p17 VALUES LESS THAN ('i','AK'),
    PARTITION p18 VALUES LESS THAN ('k','AK'),
    PARTITION p19 VALUES LESS THAN ('m','AK'),
    PARTITION p20 VALUES LESS THAN ('o','AK'),
    PARTITION p21 VALUES LESS THAN ('q','AK'),
    PARTITION p22 VALUES LESS THAN ('s','AK'),
    PARTITION p23 VALUES LESS THAN ('u','AK'),
    PARTITION p24 VALUES LESS THAN ('w','AK'),
    PARTITION p25 VALUES LESS THAN ('y','AK'),
    PARTITION p26 VALUES LESS THAN ('a','AL'),
    PARTITION p27 VALUES LESS THAN ('c','AL'),
    PARTITION p28 VALUES LESS THAN ('e','AL'),
    PARTITION p29 VALUES LESS THAN ('g','AL'),
    PARTITION p30 VALUES LESS THAN ('i','AL'),
    PARTITION p31 VALUES LESS THAN ('k','AL'),
    PARTITION p32 VALUES LESS THAN ('m','AL'),
    PARTITION p33 VALUES LESS THAN ('o','AL'),
    PARTITION p34 VALUES LESS THAN ('q','AL'),
    PARTITION p35 VALUES LESS THAN ('s','AL'),
    PARTITION p36 VALUES LESS THAN ('u','AL'),
    PARTITION p37 VALUES LESS THAN ('w','AL'),
    PARTITION p38 VALUES LESS THAN ('y','AL'),
    PARTITION p39 VALUES LESS THAN ('a','AR'),
    PARTITION p40 VALUES LESS THAN ('c','AR'),
    PARTITION p41 VALUES LESS THAN ('e','AR'),
    PARTITION p42 VALUES LESS THAN ('g','AR'),
    PARTITION p43 VALUES LESS THAN ('i','AR'),
    PARTITION p44 VALUES LESS THAN ('k','AR'),
    PARTITION p45 VALUES LESS THAN ('m','AR'),
    PARTITION p46 VALUES LESS THAN ('o','AR'),
    PARTITION p47 VALUES LESS THAN ('q','AR'),
    PARTITION p48 VALUES LESS THAN ('s','AR'),
    PARTITION p49 VALUES LESS THAN ('u','AR'),
    PARTITION p50 VALUES LESS THAN ('w','AR').....

However, this fails to work with this error:

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

What would be a valid solution for partitioning this table?

share|improve this question
    
Reverse the order to (state, city). It seems you want the partitions in that alphabetical order. –  ypercube Jan 19 '13 at 16:54
add comment

2 Answers 2

Why not add two indexes:

a) A two column index for (state, city) - handles queries for state only, then state and city

b) An index on city - queries on cities only

To tune for performance further you may be able to add lookup tables for state and city then use numeric keys to speed up query performance.

share|improve this answer
    
The question is about partitioning not about how to index the table. –  Noah Goodrich Dec 20 '12 at 6:24
1  
The user is only thinking of partitioning with regard to queries against a database table, so my answer is in attempt to solve the problem with that in mind. Why go partitioning when indexes can solve your problem? –  ssmusoke Dec 20 '12 at 6:30
add comment

Partition by range is for numeric based columns like date,int. which works like values less than 10 in partition p1 and values less than 20 in partition p2, so on. It does'nt work with varchar type columns.

So I suggest you to Go for Partition by List.

ALTER TABLE test.test PARTITION BY LIST COLUMNS (city)( partition p1 values in('miami',california'), partition p2 values in('mexico','rio'));

Refer the link below for more info. http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns-list.html

share|improve this answer
    
Double check the docs: dev.mysql.com/doc/refman/5.5/en/partitioning-columns-range.html You'll find this: CREATE TABLE employees_by_lname ( 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 (lname) ( PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE) ); –  Noah Goodrich Dec 20 '12 at 5:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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