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?

Answer 1

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