0

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:

enter image description here

MySQL Performance:

enter image description here

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.

10
  • 1
    @TheDataGuy Could you post TEXT results of SHOW CREATE TABLE table_name; for the 3 tables - register, profile, types_preference? And the TEXT results of SHOW TABLE STATUS WHERE name LIKE 'table_name'; for your 3 tables for analysis, What is typical ROWS SENT count at completion of the query? Jun 13, 2022 at 17:07
  • @WilsonHauck add the details and updated the question
    – TheDataGuy
    Jun 14, 2022 at 5:24
  • A) What is the typical rows count of delivered results? B) Will your results change significantly if you remove the line AND r.id NOT IN ( 11111 ) from the query? This is likely causing a TABLE SCAN. Jun 14, 2022 at 20:48
  • A) Max results would be 1k or less than that B)no frequent changes, I checked the query plan by removing the r.id NOT IN but no changes in the query plan, with NOT IN its a range scan, without that index scan. Range scan gives lesser rows estimate.
    – TheDataGuy
    Jun 15, 2022 at 7:38
  • Please post TEXT results of the EXPLAIN report when you run without the r.id NOT IN, Jun 15, 2022 at 11:04

1 Answer 1

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)
4
  • Thanks for your answer, just a clarification, not able to understand this In EXISTS, don't select a column. (The standard pattern is just 1.) Or not sure where to fit this
    – TheDataGuy
    Jun 21, 2022 at 5:49
  • @TheDataGuy - I changed my Answer to give the context. (It is not critical to fix it.)
    – Rick James
    Jun 21, 2022 at 6:05
  • Great, is there any reason to have index on register table in the reverse order? because in select it'll be like religion, gender, is_delete, is_verify
    – TheDataGuy
    Jun 21, 2022 at 6:12
  • @TheDataGuy - The order of things AND'd in the WHERE does not matter. More: Index Cookbook
    – Rick James
    Jun 21, 2022 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.