0

basically I have 3 identical transactional table which is contains order_id and user_id. (deposit transaction table, prepaid transaction table, and postpaid transaction table) I would to partitioning that tables. This table already filled with the data so I will alter it on production server.

Here is my create table query (same structure with postpaid and deposit table)

CREATE TABLE `t_prepaid` (
  `order_id` char(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `user_id` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_dtm` datetime DEFAULT NULL,
  `payment_dtm` datetime DEFAULT NULL,
  `expired_dtm` datetime DEFAULT NULL,
  `cancel_dtm` datetime DEFAULT NULL,
  #...
  `status` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  #...
  PRIMARY KEY (`order_id`),
  KEY `INDEX_2` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What triggers us do partitioning because sometimes (not frequently but once a week) make our mysql server hang (indicate cpu usage at 100% - we need to restart that server; also some query produce slow log query).

This is slow query:

(select `t_deposit`.`bank_code_id` as `product_master_id`,
        `t_deposit`.`order_id`     as `order_id`,
        `t_deposit`.`order_dtm`    as `order_dtm`,
        `t_deposit`.`status`       as `status`,
        "-"                              as dtl_txn,
        #...
 from `t_deposit`
 where `t_deposit`.`user_id` = "UID12543"
   and `t_deposit`.`status` <> "E"
   and date(`order_dtm`) >= "2021-10-21"
   and date(`order_dtm`) <= "2022-01-04"
)
union
(select `t_prepaid`.`product_master_id`                                           as `product_master_id`,
        `t_prepaid`.`order_id`                                                    as `order_id`,
        `t_prepaid`.`order_dtm`                                                   as `order_dtm`,
        `t_prepaid`.`status`                                                      as `status`,
        CASE
            WHEN t_prepaid.cust_name IS NULL THEN t_prepaid.cust_no
            ELSE CONCAT_WS("-", t_prepaid.cust_no, t_prepaid.cust_name) END AS dtl_txn,
        #...
 from `t_prepaid`
 where `t_prepaid`.`user_id` = "UID12543"
   and `t_prepaid`.`status` <> "E"
   and date(`order_dtm`) >= "2021-10-21"
   and date(`order_dtm`) <= "2022-01-04"
   )
union
 (select `t_postpaid`.`product_master_id`                                            as `product_master_id`,
         `t_postpaid`.`order_id`                                                     as `order_id`,
         `t_postpaid`.`order_dtm`                                                    as `order_dtm`,
         `t_postpaid`.`status`                                                       as `status`,
         CASE
             WHEN t_postpaid.cust_name IS NULL THEN t_postpaid.cust_no
             ELSE CONCAT_WS("-", t_postpaid.cust_no, t_postpaid.cust_name) END AS dtl_txn,
        #...
  from `t_postpaid`
  where `t_postpaid`.`user_id` = "UID12543"
     and `t_postpaid`.`status` <> "E"
     and date(`order_dtm`) >= "2021-10-21"
     and date(`order_dtm`) <= "2022-01-04")
 )
order by `order_dtm` asc;

This query would join 3 tables to feeding client. (page for view transaction history)

So my idea is to partitioning by user_id. this table sometimes accessed by order_id only (frequently) and sometimes by user_id.

What we have do is we add user_id as index. It would increase by 32% performance. Also we have reduced left join with another table.

here is after we add index and explain

explain with index

My question, is it good idea if I partitioning by user_id but some query required to access by order id? would it makes slower than non-partitioning table? Please your advice. thank you

3
  • my idea is to partitioning by user_id. this table sometimes accessed by order_id only (frequently) and sometimes by user_id. This means that in most cases all partitions will be investigated. This is not good idea. If most queries uses filtering by order id only then use range partitioning by this column.
    – Akina
    Jan 5 at 12:22
  • 1
    I have 3 identical transactional table which is contains order_id and user_id. (deposit transaction table, prepaid transaction table, and postpaid transaction table) This does not look like good solution... this is de-normalization, it seems.
    – Akina
    Jan 5 at 12:23
  • Do all 3 tables have PRIMARY KEY (order_id) ? Why, then, are the tables separate?
    – Rick James
    Jan 5 at 23:32
0

Replace

KEY `INDEX_2` (`user_id`)

with

KEY `INDEX_2` (`user_id`, order_dtm, status)

(Columns in that order.) That will decrease the number of rows that need to be looked at, and possibly eliminate the extra "sort" to do ORDER BY order_dtm.

And avoid un-sargable expressions:

and date(`order_dtm`) >= "2021-10-21"
and date(`order_dtm`) <= "2022-01-04"

and `order_dtm` >= "2021-10-21"
and `order_dtm`  < "2022-01-05"  -- note "<" and next day

How many rows in the tables? If less than a million, PARTITIONing is probably counterproductive.

How many different user_ids? There is a limit of the number of Partitions. Would you be doing PARTITION BY RANGE(user_id)? Or what?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.