[Maria-discuss] AUTO_INCREMENT and partitioning
Hi, 1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it? 2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb? For example: CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id), ) PARTITION BY RANGE( TO_DAYS(added) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ) ; Thanks
The only solution is to make (id, added) a PK, because the partitioning column must always belong to unique key, if there is such. In your case I would change added type to DATETIME to avoid collision. Regards On Sat, Mar 12, 2016 at 2:10 PM Ghazi Btissam <btissam.ghazi@gmail.com> wrote:
Hi,
1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it?
2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb?
For example:
CREATE TABLE new (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
added DATE,
PRIMARY KEY (id),
)
PARTITION BY RANGE( TO_DAYS(added) )
( PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
) ;
Thanks _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
You can use no primary key, and just put a regular key (not unique key) on the auto_increment column. You must make sure that manual insertions don't create a duplicate if value.
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, KEY (id), ) PARTITION BY RANGE( TO_DAYS(added) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ) ;
Look at RANGE COLUMNS for a more natural way to partition by date:
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id), ) PARTITION BY RANGE COLUMNS (added) ( PARTITION p0 VALUES LESS THAN ('1990-01-01'), .... PARTITION pmax VALUES LESS THAN MAXVALUE ) ; Sent from my iPhone
On Mar 12, 2016, at 5:16 AM, Guillaume Lefranc <guillaume.lefranc@mariadb.com> wrote:
The only solution is to make (id, added) a PK, because the partitioning column must always belong to unique key, if there is such. In your case I would change added type to DATETIME to avoid collision.
Regards
On Sat, Mar 12, 2016 at 2:10 PM Ghazi Btissam <btissam.ghazi@gmail.com> wrote: Hi,
1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it?
2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb?
For example:
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id), ) PARTITION BY RANGE( TO_DAYS(added) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ) ; Thanks
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Oops. PRIMARY KEY should be just KEY on that second example with RANGE COLUMNS. :) Sent from my iPhone
On Mar 12, 2016, at 11:01 AM, Justin Swanhart <greenlion@gmail.com> wrote:
You can use no primary key, and just put a regular key (not unique key) on the auto_increment column. You must make sure that manual insertions don't create a duplicate if value.
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, KEY (id), ) PARTITION BY RANGE( TO_DAYS(added) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ) ;
Look at RANGE COLUMNS for a more natural way to partition by date:
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id), ) PARTITION BY RANGE COLUMNS (added) ( PARTITION p0 VALUES LESS THAN ('1990-01-01'), .... PARTITION pmax VALUES LESS THAN MAXVALUE ) ; Sent from my iPhone
On Mar 12, 2016, at 5:16 AM, Guillaume Lefranc <guillaume.lefranc@mariadb.com> wrote:
The only solution is to make (id, added) a PK, because the partitioning column must always belong to unique key, if there is such. In your case I would change added type to DATETIME to avoid collision.
Regards
On Sat, Mar 12, 2016 at 2:10 PM Ghazi Btissam <btissam.ghazi@gmail.com> wrote: Hi,
1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it?
2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb?
For example:
CREATE TABLE new ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), added DATE, PRIMARY KEY (id), ) PARTITION BY RANGE( TO_DAYS(added) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ) ; Thanks
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Ghazi Btissam -
Guillaume Lefranc -
Justin Swanhart