MySQL - Optimizing a large table's join

Question

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.

Answer 1

Don't have a column for each language, have a row for each. And a column for language.

In EXISTS, don't select a column. (The standard pattern is just 1.) Change

AND EXISTS (SELECT `r`.`types` FROM ... )

to

AND EXISTS ( SELECT 1 FROM ... )

Boolean expressions return 0 for false and 1 for true, for example:

IF (vbe . id > 0, 1, 0) AS `viewed_or_not`

can be simplified to

vbe.id > 0 AS `viewed_or_not`

These Indexes may help with performance:

register:  INDEX(is_verify, is_delete, gender,
                 religion, types, paid_amnt, id)
types_preference:  INDEX(castid, userid)