optimizing slow running mysql join of 3 tables

Question

I inherted an advertising CRM application and have been having hard time fixing couple of slowness issues. One of the problem is a slow running join query of 3 tables:

orders contains products which in turn contains ads for an order. Products table serves as intermediary between the 2.

advertiser_campaign (orders) - 50,000 rows (12,000 rows in last 1 year)

CREATE TABLE `advertiser_campaign` (
   `Id` INT(10,0) NOT NULL AUTO_INCREMENT,
   `BusinessName` VARCHAR(100) NOT NULL COLLATE 'utf8_general_ci',
   `CreatedAt` DATETIME NOT NULL,
   `account_id` INT(10,0) NULL DEFAULT NULL,
   `is_executed` TINYINT(1) NOT NULL DEFAULT '0',
   `order_status` INT(10,0) NULL DEFAULT NULL,
   `created_by` INT(10,0) NULL DEFAULT NULL,
   PRIMARY KEY (`Id`) USING BTREE,
   INDEX `FK_AvertiserCampaign_AccountId` (`account_id`) USING BTREE,
   INDEX `CreatedAt` (`CreatedAt`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=53385;

newspaper_product_bid (products) - 56,000 rows (13,000 rows in last 1 year)

CREATE TABLE `newspaper_product_bid` (
`Id` INT(10,0) NOT NULL AUTO_INCREMENT,
`AdvertiserCampaignId` INT(10,0) NOT NULL,
`Status` INT(10,0) NOT NULL DEFAULT '10',
`CreatedAt` DATETIME NOT NULL,
PRIMARY KEY (`Id`) USING BTREE,
INDEX `FK_NewspaperProductBid_AdvertiserCampaign_idx` (`AdvertiserCampaignId`) USING BTREE,
CONSTRAINT `FK_NewspaperProductBid_AdvertiserCampaign` FOREIGN KEY (`AdvertiserCampaignId`) REFERENCES `iihssp_prod_20210820`.`advertiser_campaign` (`Id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=59246;

insert_usage (ads) - 82,000 rows (18,000 rows in last 1 year)

CREATE TABLE `insert_usage` (
`insert_usage_id` INT(10,0) NOT NULL AUTO_INCREMENT,
`publish_date` DATE NULL DEFAULT NULL,
`published` TINYINT(1) NULL DEFAULT NULL COMMENT 'Was this specific ad published',
`bid_id` INT(10,0) NULL DEFAULT NULL,
`ad_status` INT(10,0) NULL DEFAULT NULL,
PRIMARY KEY (`insert_usage_id`) USING BTREE,
INDEX `newspaper_product_bid.id` (`bid_id`) USING BTREE,
INDEX `publish_date` (`publish_date`) USING BTREE,
INDEX `ix_ad_status` (`ad_status`) USING BTREE,
INDEX `published` (`published`) USING BTREE,
---------------------------------------
-- Add this index didn't help either
---------------------------------------
-- INDEX `bid_id_ad_status` (`bid_id`, `ad_status`) USING BTREE,
CONSTRAINT `newspaper_product_bid.id` FOREIGN KEY (`bid_id`) REFERENCES `iihssp_prod_20210820`.`newspaper_product_bid` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=100821;

Here's my simplified join statement (part of a bigger stored procedure) to get sales data:

SELECT 
    DATE_FORMAT(iu.publish_date, '%m-%d') AS CurrDate,
    ROUND(ifnull(sum(ifnull(iu.ad_price, 0)),0),2) as Printed
FROM insert_usage iu
JOIN newspaper_product_bid as npb on iu.bid_id = npb.id 
     -- The cuplrit (slows down query by 200%)
     AND(iu.ad_status != 7 || iu.ad_status is null) AND npb.`Status` != -10
JOIN advertiser_campaign ac on ac.Id = npb.AdvertiserCampaignId
WHERE iu.published=1 
AND DATE(iu.publish_date) BETWEEN  DATE_FORMAT(QtrStartDt, '%Y-%m-01') AND CurrentDt
GROUP BY DATE_FORMAT(iu.publish_date, '%m-%d')

I already have indexes on all columns involved in the join.

Explaining the query gives this:

The moment I remove check on iu.ad_status and npb.status, query runs fast and uses proper indexes.

How can I improve the speed of the query?

user_1766831 · Answer

There is no need for DATE(iu.publish_date); you can simply use iu.publish_date in that context. When hiding a column inside a function call (even DATE()) it becomes not 'sargeable' and the Optimizer can't see it.

After that change, this index may help iu: Drop INDEX published (published) USING BTREE and Add

INDEX(published, publish_date)

advertiser_campaign seems to be unused; consider removing the JOIN to it.

For readability, please use ON for defining how tables relate; use WHERE for filtering. That is, move this to WHERE:

 AND(iu.ad_status != 7 || iu.ad_status is null) AND npb.`Status` != -10

(For consistency with "AND", please use OR instead of ||.)

CurrentDt seems to be missing from this "simplification". Perhaps it is simply NOW()?

I would guess that those changes would convince the Optimizer to use my suggested index instead of either of the query plans you showed. Then, if that is selective enough, it will run faster.

optimizing slow running mysql join of 3 tables

1 Answers