[Solved] MYSQL: slow performance on join tables

EverSQL Database Performance Knowledge Base

MYSQL: slow performance on join tables

Database type:

I have this 3 tables, but when I join with the ref_practice_invoice_payment the query takes longer (about 5 seconds++).

The query is as follow:

SELECT * 
  FROM practice_invoice_detail PID 
  LEFT 
  JOIN ref_practice_invoice_payment RPIP 
    ON RPIP.timestamp = PID.timestamp 
   AND RPIP.practice_id = PID.practice_id
   AND RPIP.refunded <> 1 
   AND RPIP.other_bill = 0 
  JOIN practice_invoice_header PIH 
    ON PIH.timestamp = PID.timestamp 
   AND PIH.practice_id = PID.practice_id 
   AND PIH.is_active = 1
WHERE PIH.source = 'E' 
   AND PID.practice_id = 28618 
   AND (
         (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
       OR (PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%')
           )

Below is the schema:

CREATE TABLE `practice_invoice_header` (
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `cal_id` int(11) NOT NULL,
 `source` char(1) NOT NULL COMMENT 'E = ENCOUNTER; P = OTHER (PHARM / LAB)',
 `total_invoice` float NOT NULL DEFAULT '0',
 `total_procedure` float NOT NULL DEFAULT '0',
 `total_pharmacy` float NOT NULL DEFAULT '0',
 `extra_charge_ph` float NOT NULL DEFAULT '0',
 `total_lab` float NOT NULL DEFAULT '0',
 `total_voucher` float NOT NULL DEFAULT '0',
 `total_base` float NOT NULL DEFAULT '0',
 `procedure_base` float NOT NULL DEFAULT '0',
 `pharmacy_base` float NOT NULL DEFAULT '0',
 `lab_base` float NOT NULL DEFAULT '0',
 `clinic_share` float NOT NULL DEFAULT '0',
 `tax` float NOT NULL DEFAULT '0',
 `other_bill` float NOT NULL DEFAULT '0',
 `changed` float NOT NULL DEFAULT '0',
 `paid` float NOT NULL DEFAULT '0',
 `covered_amount` float NOT NULL DEFAULT '0',
 `reff_id` bigint(20) NOT NULL,
 `notes` varchar(300) DEFAULT NULL,
 `custom_invnum` varchar(30) DEFAULT NULL,
 `insurance_plan_id` varchar(20) DEFAULT NULL,
 `outpx_id` bigint(20) DEFAULT NULL,
 `is_active` int(11) NOT NULL DEFAULT '1',
 `cancel_reason` varchar(200) DEFAULT NULL,
 `pharm_read` int(11) NOT NULL DEFAULT '0',
 `lab_read` int(11) NOT NULL DEFAULT '0',
 `rad_read` int(11) NOT NULL DEFAULT '0',
 `ph_checked_by` int(11) NOT NULL DEFAULT '0',
 `ph_checked_time` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`timestamp`,`practice_id`),
 KEY `source` (`source`),
 KEY `reff_id` (`reff_id`),
 KEY `practice_id` (`practice_id`),
 KEY `timestamp` (`timestamp`),
 KEY `is_active` (`is_active`),
 KEY `custom_invnum` (`custom_invnum`),
 KEY `insurance_plan_id` (`insurance_plan_id`),
 KEY `practice_id_3` (`practice_id`,`reff_id`),
 KEY `ph_check_status` (`ph_checked_by`),
 KEY `cal_id` (`cal_id`),
 KEY `outpx_id` (`outpx_id`),
 KEY `practice_id_8` (`practice_id`,`source`,`reff_id`,`is_active`),
 KEY `total_invoice` (`total_invoice`),
 CONSTRAINT `practice_invoice_header_ibfk_1` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `practice_invoice_detail` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `item_id` int(11) NOT NULL,
 `item_sub_id` int(11) DEFAULT NULL,
 `item_type` char(1) NOT NULL COMMENT 'D = DRUG; P = PROCEDURE; L = LAB',
 `item_qty` float NOT NULL,
 `item_price` float(22,2) NOT NULL,
 `discount` float NOT NULL DEFAULT '0',
 `item_comission` float NOT NULL DEFAULT '0',
 `item_comission_type` char(1) NOT NULL DEFAULT '%',
 `doctor_id` int(11) NOT NULL DEFAULT '0',
 `item_comission_2` float NOT NULL DEFAULT '0',
 `item_comission_2_type` char(1) NOT NULL DEFAULT '%',
 `doctor_id_2` int(11) NOT NULL DEFAULT '0',
 `item_base_price` float(22,2) NOT NULL DEFAULT '0.00',
 `extra_base` float(22,2) NOT NULL DEFAULT '0.00',
 `clinic_share` float NOT NULL DEFAULT '0',
 `extra_charge` float NOT NULL DEFAULT '0',
 `referred_by` varchar(30) DEFAULT NULL,
 `referred_type` char(1) DEFAULT NULL,
 `referred_comission` float NOT NULL DEFAULT '0',
 `pm_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `item_type` (`item_type`),
 KEY `timestamp` (`timestamp`,`practice_id`),
 KEY `practice_id` (`practice_id`),
 KEY `item_id_2` (`item_id`,`item_sub_id`,`item_type`),
 KEY `pm_id` (`pm_id`),
 KEY `timestamp_2` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`),
 KEY `practice_id_2` (`practice_id`,`referred_by`,`referred_type`),
 KEY `practice_id_3` (`practice_id`,`item_type`),
 KEY `the_id` (`id`,`practice_id`) USING BTREE,
 KEY `item_comission_type` (`item_comission_type`),
 KEY `item_comission` (`item_comission`),
 KEY `doctor_id` (`doctor_id`),
 KEY `item_comission_2` (`item_comission_2`),
 KEY `item_comission_2_type` (`item_comission_2_type`),
 KEY `doctor_id_2` (`doctor_id_2`),
 KEY `group_id` (`id`,`timestamp`,`practice_id`) USING BTREE,
 KEY `timestamp_3` (`timestamp`,`practice_id`,`item_type`,`item_comission`,`item_comission_type`,`doctor_id`,`item_id`,`item_sub_id`,`id`) USING BTREE,
 KEY `timestamp_4` (`timestamp`,`practice_id`,`item_id`,`item_sub_id`,`item_type`,`item_comission_2`,`item_comission_2_type`,`doctor_id_2`,`id`) USING BTREE,
 CONSTRAINT `practice_invoice_detail_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
 CONSTRAINT `practice_invoice_detail_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=968763 DEFAULT CHARSET=latin1

CREATE TABLE `ref_practice_invoice_payment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `timestamp` bigint(20) NOT NULL,
 `practice_id` int(11) NOT NULL,
 `payment_method` int(11) NOT NULL,
 `pay` float NOT NULL,
 `changed` float NOT NULL DEFAULT '0',
 `extra_amount` float NOT NULL DEFAULT '0',
 `pay_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `pay_cal_id` int(11) NOT NULL DEFAULT '0',
 `user_id` int(11) NOT NULL,
 `user_type` int(11) NOT NULL,
 `refunded` int(11) NOT NULL DEFAULT '0',
 `payment_note` varchar(200) DEFAULT NULL,
 `extra` varchar(15) DEFAULT NULL,
 `other_bill` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `timestamp` (`timestamp`),
 KEY `practice_id` (`practice_id`),
 KEY `payment_method` (`payment_method`),
 KEY `user_id` (`user_id`),
 KEY `user_type` (`user_type`),
 KEY `timestamp_2` (`timestamp`,`practice_id`),
 KEY `refunded` (`refunded`),
 KEY `pay` (`pay`),
 KEY `extra_amount` (`extra_amount`),
 KEY `extra` (`extra`),
 KEY `pay_date` (`pay_date`),
 KEY `pay_cal_id` (`pay_cal_id`),
 KEY `other_bill` (`other_bill`),
 KEY `timestamp_3` (`timestamp`,`practice_id`,`refunded`,`other_bill`,`pay_cal_id`) USING BTREE,
 CONSTRAINT `ref_practice_invoice_payment_ibfk_1` FOREIGN KEY (`timestamp`) REFERENCES `practice_invoice_header` (`timestamp`) ON DELETE CASCADE,
 CONSTRAINT `ref_practice_invoice_payment_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `practice_place` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=255913 DEFAULT CHARSET=latin1

And here is the EXPLAINation of the query above

1   SIMPLE  PIH ref PRIMARY,source,practice_id,timestamp,is_active,practice_id_3,cal_id,practice_id_5,practice_id_6,practice_id_2,practice_id_4,practice_id_7,practice_id_8,total_invoice   practice_id_6   4   const   40228   Using index condition   
1   SIMPLE  PID ref timestamp,practice_id,timestamp_2,practice_id_2,practice_id_3,item_comission_type,timestamp_3,timestamp_4   timestamp   12  k6064619_lokadok.PIH.timestamp,const    1       
1   SIMPLE  RPIP    ref timestamp,practice_id,timestamp_2,refunded,other_bill,timestamp_3   timestamp   8   k6064619_lokadok.PIH.timestamp  1   Using where 

The join query all is SIMPLE, yet the query took a litle bit too long. However, the data is pretty much a lot but still decent I suppose. About 200.000 rows

FURTHER FINDINGS: Strange thing is if I remove this part, the query is fast (below 1 sec)

(RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 

@Eperbab

Below is the explain of the suggested query:

1   SIMPLE  practice_invoice_header index_merge PRIMARY,source,practice_id,timestamp,is_active,practice_id_3,cal_id,practice_id_5,practice_id_6,practice_id_2,practice_id_4,practice_id_7,practice_id_8,total_invoice,practice_id_9 practice_id_9,practice_id   9,4 NULL    7422    Using intersect(practice_id_9,practice_id); Using where 
1   SIMPLE  practice_invoice_detail ref timestamp,practice_id,timestamp_2,practice_id_2,practice_id_3,item_comission_type,timestamp_3,timestamp_4   timestamp   12  k6064619_lokadok.practice_invoice_header.timestamp,const    1       
1   SIMPLE  ref_practice_invoice_payment    ref timestamp,practice_id,timestamp_2,refunded,other_bill,timestamp_3,practice_id_2 timestamp   8   k6064619_lokadok.practice_invoice_header.timestamp  1   Using where

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `practice_invoice_detail` ADD INDEX `practice_detail_idx_practice_id_timestamp` (`practice_id`,`timestamp`);
ALTER TABLE `practice_invoice_header` ADD INDEX `practice_header_idx_source_is_active` (`source`,`is_active`);
ALTER TABLE `ref_practice_invoice_payment` ADD INDEX `ref_invoice_idx_other_timesta_practic_refunde` (`other_bill`,`timestamp`,`practice_id`,`refunded`);
The optimized query:
SELECT
        * 
    FROM
        practice_invoice_detail PID 
    LEFT JOIN
        ref_practice_invoice_payment RPIP 
            ON RPIP.timestamp = PID.timestamp 
            AND RPIP.practice_id = PID.practice_id 
            AND RPIP.refunded <> 1 
            AND RPIP.other_bill = 0 
    JOIN
        practice_invoice_header PIH 
            ON PIH.timestamp = PID.timestamp 
            AND PIH.practice_id = PID.practice_id 
            AND PIH.is_active = 1 
    WHERE
        PIH.source = 'E' 
        AND PID.practice_id = 28618 
        AND (
            (
                RPIP.pay_cal_id >= 201805130 
                AND RPIP.pay_cal_id <= 201805200
            ) 
            OR (
                PIH.cal_id >= 201805130 
                AND PIH.cal_id <= 201805200 
                AND PIH.total_invoice = 0 
                AND PID.item_comission_type <> '%'
            )
        )

Related Articles



* original question posted on StackOverflow here.