Im using the following query. This eat my complete CPU in Aurora Cluster. Also too much of wait. I tried by tunning index, but still no luck. Is there any recommendation to make it perform better?
Query:
SELECT `r` . `id`,
`r` . `name`,
`r` . `dob`,
`r` . `gender`,
`r` . `photo_show`,
`p` . `user_id`,
`p` . `stateother`,
`p` . `districtother`,
`p` . `cityother`,
`p` . `occupation`,
`p` . `qualification`,
`p` . `country`,
'success'
AS `success`,
`verified_photo`
AS `view_photo`,
CASE
WHEN `r` . `marital_status` = 1 THEN 'Never Married'
WHEN `r` . `marital_status` = 2 THEN 'Widowed'
WHEN `r` . `marital_status` = 3 THEN 'Divorced'
WHEN `r` . `marital_status` = 4 THEN 'Awaiting divorce'
WHEN `r` . `marital_status` = 5 THEN 'Not-Specified'
ELSE ''
end
AS `marital_status_name`,
r.`paid_amnt`,
IF (`p` . `country` > 0, (SELECT co . `english`
FROM `country` co
WHERE co . id = `p` . `country`), `p` . `country`
) AS
`country_name`,
IF (`p` . `state` > 0, (SELECT st . `english`
FROM `state` st
WHERE st . id = `p` . `state`), `p` . `state`)
AS
`state_name`,
IF (`p` . `district` > 0, (SELECT dt . `english`
FROM `district` dt
WHERE dt . id = `p` . `district`), `p` .
`district`) AS
`district_name`,
IF (`p` . `city` > 0, (SELECT ct . `english`
FROM `city` ct
WHERE ct . id = `p` . `city`), `p` . `city`)
AS `city_name`,
IF (`p` . `qualification` > 0, (SELECT pqu . `english`
FROM `qualification` pqu
WHERE pqu . id = `p` . `qualification`),
`p` .
`qualification`)
AS `qualification_name`,
IF (`p` . `occupation` > 0, (SELECT oc . `english`
FROM `occupation` oc
WHERE oc . id = `p` . `occupation`), `p` .
`occupation`)
AS `occupation_name`,
IF (vbe . id > 0, 1, 0)
AS `viewed_or_not`,
IF (r . `verified_photo` != '', 1, 0)
AS `photo_uploaded_ro_not`
FROM `register` r
JOIN `profile` `p`
ON `r` . `id` = `p` . `user_id`
JOIN `types_preference` `cp`
ON `cp`.`userid` = `r`.`id`
LEFT JOIN `viewed_by_me` vbe
ON vbe . userid = 123123
AND r . `id` = vbe . `viewid`
WHERE `cp`.`castid` = 451
AND EXISTS (SELECT `r`.`types`
FROM `types_preference` `cpre`
WHERE `r`.`types` = `cpre`.`castid`
AND `cpre`.`userid` = 123123)
AND 1
AND ( 1 )
AND ( `r` . `dob` BETWEEN '1995-09-23' AND '2005-09-23' )
AND `r`.`id` NOT IN ( 11111 )
AND r.`religion` = 1
AND r.`gender` = 2
AND r.`is_delete` = 0
AND `r`.`is_verify` = 1
GROUP BY `r`.`id`
ORDER BY `viewed_or_not` ASC,
`photo_uploaded_ro_not` DESC,
`verify_date` DESC
LIMIT 15 offset 0
Explain:
MySQL Performance:
Total Rows in register table: 643457
Table DDL: https://pastebin.com/mSCqxefq
Table status: https://pastebin.com/nRa70Jqh
Server details:
Its AWS Aurora with 5.7 8 core CPU 64GB memory SSD with high IOPS(it can scale to any limit)
The number of tables 450.
r
.id
NOT IN ( 11111 ) from the query? This is likely causing a TABLE SCAN.r.id NOT IN
but no changes in the query plan, withNOT IN
its a range scan, without that index scan. Range scan gives lesser rows estimate.