MySQL partitioning by table rows

Question

I create a table as below

CREATE TABLE `Archive_MasterLog` (
  `LogID` INT(10) NOT NULL AUTO_INCREMENT,
  `LogDate` DATETIME NULL,
  `AssessorName` VARCHAR(255) NULL,
  `TblName` VARCHAR(100) NULL,
  PRIMARY KEY (`LogID`),
  UNIQUE INDEX `Index_72491D22_3806_4A01` (`LogID`)
)
ENGINE = INNODB;

I want to partitioning this table by number of rows of table ==> every of 100K rows will create a new partition.

How can do it from MySQL?

I think the closest you can come is by range partitioning on LogId. This is not perfect, because the values are not 100% guaranteed to have no holes, but it is probably fine for most purposes. — Gordon Linoff
– Gordon Linoff, Commented Aug 4, 2015 at 11:47

Rick James · Accepted Answer · 2015-08-04 18:57:16Z

1

Why? You will probably gain no benefits from PARTITIONing.

Will you be purging old data? If so, the partition on LogDate. Then we can discuss how to purge.

You have two keys on the same pair of rows, keep the PRIMARY KEY, toss the UNIQUE key.

You have an index on RecordID, but that column does not exist??

answered Aug 4, 2015 at 18:57

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quan Nguyen Over a year ago

Hi Rick, I update the question to remove RecordID. But I think you're right, I should partition by LogDate

Quan Nguyen · Accepted Answer · 2015-08-05 01:50:05Z

0

The problem comes from the frequently of data. Some months or weeks we have more than 2M rows/month but others month we have less than 10K rows. I reviewed the data and found that the we should partition by LogID

The reasion also comes from the customer. They don't want to change the the key of table.

Here's my solution

CREATE TABLE `ULPAT`.`MasterLog` (
  `LogID` INT(10) NOT NULL AUTO_INCREMENT,
  `LogDate` DATETIME NULL,
  `AssessorName` VARCHAR(255) NULL,
  `TblName` VARCHAR(100) NULL,
  PRIMARY KEY (`LogID`),

  INDEX `LogID` (`LogID`)
)
ENGINE = INNODB
PARTITION BY HASH(mod(ceiling(LogID*0.0000005), 400))
PARTITIONS 400;

I think this is not the best solution but work for me.

Thanks

answered Aug 5, 2015 at 1:50

Quan Nguyen

5386 gold badges23 silver badges48 bronze badges

6 Comments

Quan Nguyen Over a year ago

This solution not work for MySQL 5.5 or higher. The meessage is "Error Code: 1564. This partition function is not allowed"

Rick James Over a year ago

You have a redundant index -- A PRIMARY KEY is an index; remove the INDEX(LogID).

Rick James Over a year ago

400 partitions is a lot -- and it leads to inefficiencies.

Rick James Over a year ago

HASH partitioning is useless.

Quan Nguyen Over a year ago

@RickJames: Please let me know why HASH partitioning is useless

|

Collectives™ on Stack Overflow

MySQL partitioning by table rows

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related