MySQL optimization with joins, group by with large number of data-CodePudding

The following query takes a whopping 6 seconds to execute and I can't seem to figure out why. I have an index on the table. But like it doesn't do much to speed up the query.

Query :

SELECT `AD`.`id`, `CAM`.`cam_name`, `CUI`.`cui_id`, `CAM`.`cam_id`, `AD`.`api_json_response_data` AS `refused_by_api`
FROM `tbl_api_data` AS `AD`
LEFT JOIN `tbl_camp_user_info` AS `CUI` ON `AD`.`cui_id` = `CUI`.`cui_id`
JOIN `tbl_campaign` AS `CAM` ON `CAM`.`cam_id` = `CUI`.`cui_campaign_id`
JOIN `tbl_usr_lead_setting` AS `ULS` ON `CUI`.`cui_id` = `ULS`.`cui_id`
WHERE `CUI`.`cui_status` = 'active'
AND `CAM`.`cam_status` = 'active'
AND `ULS`.`uls_status` = 'active'
AND `AD`.`status` = 'error'
AND `CUI`.`cui_cron_status` = '1'
AND `CUI`.`cui_created_date` >= '2021-07-01 00:00:00'
GROUP BY `AD`.`cui_id`

I have an index on the below table:

tbl_api_data - id,cui_id
tbl_camp_user_info - cui_id,cui_campaign_id,cui_cron_status   (cui_status - not)
tbl_campaign - cam_id, cam_status
tbl_usr_lead_setting - cui_id,uls_status

index image

Total number of record in each table :

tbl_api_data - 297,297 rows
tbl_camp_user_info - 843,390 rows
tbl_campaign - 334 rows
tbl_usr_lead_setting - 879,390 rows

And query Result has 376 rows.

If I have used limit on above query like below.Result is 1o rows But it will take 8.278 sec.That's also too much.

SELECT `AD`.`id`, `CAM`.`cam_name`, `CUI`.`cui_id`, `CAM`.`cam_id`, `AD`.`api_json_response_data` AS `refused_by_api`
    FROM `tbl_api_data` AS `AD`
    LEFT JOIN `tbl_camp_user_info` AS `CUI` ON `AD`.`cui_id` = `CUI`.`cui_id`
    JOIN `tbl_campaign` AS `CAM` ON `CAM`.`cam_id` = `CUI`.`cui_campaign_id`
    JOIN `tbl_usr_lead_setting` AS `ULS` ON `CUI`.`cui_id` = `ULS`.`cui_id`
    WHERE `CUI`.`cui_status` = 'active'
    AND `CAM`.`cam_status` = 'active'
    AND `ULS`.`uls_status` = 'active'
    AND `AD`.`status` = 'error'
    AND `CUI`.`cui_cron_status` = '1'
    AND `CUI`.`cui_created_date` >= '2021-07-01 00:00:00'
    GROUP BY `AD`.`cui_id`
    LIMIT 10

I'm stuck on this for last 1 week. I really need to optimize the above query. Please help me today if possible. Help would be appreciated. Thank you.

CodePudding user response：

Going by what you posted, you have a composite index on tbl_api_data - id,cui_id. In the SQL you are joining this table with another table using "cui_id" field and you are also using this field for group by. However, you havent added index on this field. That can be a reason. Remember that the composite index you posted cant be used for this join and group by because "cui_id" is not the leftmost field (or first field in composite index). So try adding a separate index on "cui_id"

CodePudding user response：

That seems to be a JOIN, not a LEFT JOIN.

These composite indexes may help:

AD:  INDEX(status, cui_id,  id, api_json_response_data)
CUI:  INDEX(cui_status, cui_cron_status, cui_created_date,
            cui_id,  cui_campaign_id)
CAM:  INDEX(cam_status, cam_id,  cam_name)
ULS:  INDEX(uls_status, cui_id)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

A LIMIT without and ORDER BY can lead to any random subset of the rows being returned.

"tbl_api_data - id,cui_id" -- Assuming that id is the PRIMARY KEY, this index is likely to be useless. That is, don't start a secondary index with the PK's columns(s).