MySQL 5.0.23+partition

[carlh]

Whats the proper syntax for partitioning a database in mysql? I'm using version 5.0.23, and all the samples i found have it formatted like this, but when I try to run it it says there is an error in the code


alter table `dnc`.`fed`
partition by range (`fullphone`)
(PARTITION p1 VALUES LESS THAN (1000000000),
PARTITION p2 VALUES LESS THAN (2000000000),
PARTITION p3 VALUES LESS THAN (3000000000),
PARTITION p4 VALUES LESS THAN (4000000000),
PARTITION p5 VALUES LESS THAN (5000000000),
PARTITION p6 VALUES LESS THAN (6000000000),
PARTITION p7 VALUES LESS THAN (7000000000),
PARTITION p8 VALUES LESS THAN (8000000000),
PARTITION p9 VALUES LESS THAN (9000000000),
PARTITION p10 VALUES LESS THAN (10000000000));

The error text is:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'partition by range(`fullphone`)
(PARTITION p1 VALUES LESS THAN (1000000000' at line 2

and it's error code #1064

and here's the sql for the create table:

CREATE TABLE `dnc`.`fed` (
`areacode` varchar(3) default NULL,
`phone` varchar(7) default NULL,
`fullphone` bigint(10) default NULL,
`dwnld_date` varchar(10) default NULL,
`qtr` varchar(1) default NULL,
`yr` varchar(2) default NULL,
UNIQUE KEY `U_FED` (`fullphone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


edit: now that i'm re-reading it i think i might need to set the fullphone field to a primary key instead of unique key? The reason I had that setup as a unique key is sometimes the files i need to import to the table contain duplicates, so when i run my import with a unique key i can replace or ignore those records, but with a primary key it halts and gives an error

[chazzy]

Yes, MySQL partitioning requires primary key to be used.

[carlh]

well i changed the key to a primary key on fullphone, but it's still giving me the same error when i try to run the partition, if i have trouble importing i can always change that around, but from what i've read a partition will help speed up my search a ton, since there's millions of records and i need to run a search on another table against the fullphone field in this one

altered table is:

CREATE TABLE `dnc`.`fed` (
`areacode` varchar(3) default NULL,
`phone` varchar(7) default NULL,
`fullphone` bigint(10) NOT NULL default '0',
`dwnld_date` varchar(10) default NULL,
`qtr` varchar(1) default NULL,
`yr` varchar(2) default NULL,
PRIMARY KEY (`fullphone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


edit: again after doing some digging it looks like this version has problems with partitioning when running on Windows. It's build 5.0.23 and the site has 5.0.24, but when looking through the MySQL forums I see people referencing 5.6 and 5.7 bug fixes, are there 2 different version numbers?

[chazzy]

Ok. It helps when I actually open my eyes.

When I talk about partitioning, i'm talking 5.1 mysql, that uses this partitioning:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

in 5.0, mysql has clustering - which is load balancing of data across multiple physical servers. if you're talking partitioning, that's a 5.1 feature, not a 5.0 feature.