Mariadb Table Subpartitioning

Question

I Have the following schema for mariadb I wanted to partion on paid column and then further subpartition on branch column.

• My schema for an existing table is follows:

CREATE TABLE `o_loans` (
  `uid` int NOT NULL AUTO_INCREMENT,
  `loan_code` varchar(30) DEFAULT NULL COMMENT 'Custom loan code',
  `customer_id` int NOT NULL,
  `group_id` int DEFAULT '0',
  `asset_id` int NOT NULL DEFAULT '0',
  `account_number` varchar(30) NOT NULL COMMENT 'E.g. mobile, bank',
  `enc_phone` varchar(70) DEFAULT NULL,
  `product_id` int NOT NULL,
  `loan_type` int NOT NULL DEFAULT '0',
  `loan_amount` double(50,2) NOT NULL,
  `disbursed_amount` double(50,2) NOT NULL,
  `total_repayable_amount` double(50,2) NOT NULL,
  `total_repaid` double(50,2) NOT NULL,
  `loan_balance` double(50,2) NOT NULL,
  `period` int NOT NULL,
  `period_units` varchar(30) NOT NULL,
  `payment_frequency` varchar(30) NOT NULL,
  `payment_breakdown` varchar(75) NOT NULL,
  `total_addons` double(50,2) NOT NULL,
  `total_deductions` double(50,2) NOT NULL,
  `total_instalments` int NOT NULL COMMENT 'Total installments in this loan',
  `total_instalments_paid` int NOT NULL COMMENT 'How many instalments have been paid',
  `current_instalment` int NOT NULL COMMENT 'We are in instalment number?',
  `current_instalment_amount` double(50,2) DEFAULT NULL,
  `income_earned` double(50,2) DEFAULT '0.00',
  `given_date` date NOT NULL,
  `next_due_date` date NOT NULL,
  `final_due_date` date NOT NULL,
  `added_by` int NOT NULL,
  `current_agent` int NOT NULL,
  `current_lo` int DEFAULT NULL,
  `current_co` int DEFAULT NULL,
  `allocation` varchar(10) DEFAULT 'BRANCH',
  `current_branch` int NOT NULL,
  `added_date` datetime NOT NULL,
  `loan_stage` int NOT NULL,
  `loan_flag` int NOT NULL COMMENT 'from o_flags',
  `transaction_code` varchar(40) NOT NULL,
  `transaction_date` datetime NOT NULL,
  `application_mode` varchar(40) NOT NULL COMMENT 'MANUAL, APP, USSD, SMS',
  `disburse_state` varchar(45) DEFAULT 'NONE' COMMENT 'NONE, INITIATED, DELIVERED, FAILED, REVERSED',
  `disbursed` int DEFAULT '0' COMMENT 'Whether the loan was given or not',
  `paid` int NOT NULL DEFAULT '0' COMMENT 'paid or not. each state encapsulates multiple statuses',
  `other_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `status` int NOT NULL COMMENT 'From o_loan_statuses',
  PRIMARY KEY (`uid`,`paid`),
  KEY `customer_id` (`customer_id`),
  KEY `asset_id` (`asset_id`),
  CONSTRAINT `o_loans_chk_1` CHECK (json_valid(`other_info`))
);

• I'm trying to use the following query to achieve the goal:

    ALTER TABLE o_loans PARTITION BY LIST (paid) SUBPARTITION BY RANGE (branch) (
        PARTITION p0 VALUES IN (0) (
          SUBPARTITION p0_1 VALUES LESS THAN (2),
          SUBPARTITION p0_2 VALUES LESS THAN (4),
          SUBPARTITION p0_3 VALUES LESS THAN (6),
          SUBPARTITION p0_4 VALUES LESS THAN (8),
          SUBPARTITION p0_5 VALUES LESS THAN (10),
          SUBPARTITION p0_6 VALUES LESS THAN (12),
          SUBPARTITION p0_7 VALUES LESS THAN (14),
          SUBPARTITION p0_8 VALUES LESS THAN (16),
          SUBPARTITION p0_9 VALUES LESS THAN (18),
          SUBPARTITION p0_10 VALUES LESS THAN (20),
          SUBPARTITION p0_overflow VALUES LESS THAN MAXVALUE
        ),
        PARTITION p1 VALUES IN (1) (
          SUBPARTITION p1_1 VALUES LESS THAN (2),
          SUBPARTITION p1_2 VALUES LESS THAN (4),
          SUBPARTITION p1_3 VALUES LESS THAN (6),
          SUBPARTITION p1_4 VALUES LESS THAN (8),
          SUBPARTITION p1_5 VALUES LESS THAN (10),
          SUBPARTITION p1_6 VALUES LESS THAN (12),
          SUBPARTITION p1_7 VALUES LESS THAN (14),
          SUBPARTITION p1_8 VALUES LESS THAN (16),
          SUBPARTITION p1_9 VALUES LESS THAN (18),
          SUBPARTITION p1_10 VALUES LESS THAN (20),
          SUBPARTITION p1_overflow VALUES LESS THAN MAXVALUE
        )
      );

• Error I'm getting is that "RANGE" is not expected at this position, expecting HASH, KEY. Someone please guide me in getting things right. I'm new in this concept.

Answer 1

There are limitations on what partitioning types can be used in a SUBPARTITION.

In my experience, none of them are useful. For that matter, partitioning have very few uses. See Partition

PARTITION BY RANGE is useful for deleting "old" rows, but does not otherwise help performance. However that does not seem to be necessary in your case.

Instead of partitionng, use composite indexes. For example:

INDEX(current_branch, customer_id, paid)

For more tailored suggestions, let's see some of the important SELECTs.

Another tip. double(50,2) is a binary storage form with only about 16 significant digits -- after rounding to 2 decimal places. The (m,n) is deprecated because it is useless. I recommend DECIMAL(11,2) which will exactly hold decimal amounts up to a billion (dollars/Euros/etc) with precision of 2 decimal places. Increase the 11 if needed.