Why is this SELECT statement with nested WHERE subqueries running slowly in MYSQL?

Question

I wrote a complex query that deals with permissions, and in order to make it work, it contains a nested sub-query inside of an IF statement to match only those items that the user has permissions for, and match the conditions.

I am unsure why it runs slowly, and would appreciate help on how to investigate the speed of the query.

As an outline of what this looks like: (Full Query Below)

//Main table has many records

SELECT some field FROM tbl join some table WHERE

IF (

Subquery ( Sub-subquery () )

)

Full Query:

            -- Get Account By User Assign to
            SELECT 
            --  SQL_CALC_FOUND_ROWS acc.account_id,
                acc.`is_delete`,
                acc.test,   CONCAT('<strong>',acc.account_name,'</strong>') AS account_name_alias,
                CONCAT(u.first_name,' ',u.last_name) created_by_alias, 
                a.account_type_name,
                i.industry_name, 
                CONCAT(u1.first_name,' ',u1.last_name) 
                modified_by_alias,
                isp.isp_name,
                sp.service_plan_name,
                c.country_name,
                ci.city_name,
                d.district_name,
                co.commune_name

            FROM crm_accounts AS acc
            INNER JOIN crm_accounts_assignments asm ON acc.`account_id` = asm.`account_id`
            LEFT JOIN crm_users u ON acc.created_by = u.user_id
            LEFT JOIN crm_users u1 ON  acc.modified_by = u1.user_id
            LEFT JOIN crm_account_types a ON  acc.account_type = a.account_type_id
            LEFT JOIN crm_industries i ON  acc.industry_id = i.industry_id
            LEFT JOIN crm_isp_lists isp ON  acc.isp_id = isp.isp_id
            LEFT JOIN crm_service_plans sp ON  acc.service_plan_id = sp.service_plan_id
            LEFT JOIN crm_countries c ON  acc.country_id = c.country_id
            LEFT JOIN crm_cities ci ON  acc.city_id = ci.city_id
            LEFT JOIN crm_districts d ON  acc.district_id = d.district_id
            LEFT JOIN crm_communes co ON  acc.commune_id = co.commune_id
            WHERE acc.is_delete = 0  

            //This my sub query
            AND 

                IF (
                      (
                          CASE asm.`assign_type` 
                        WHEN 
                          EXISTS
                             (
                              SELECT 1 FROM crm_user_param_users upu1
                              INNER JOIN crm_users us1 ON upu1.`user1_id` = us1.`user_id`
                              WHERE upu1.`user_id` = 7  AND  upu1.`user1_id` = asm.`assign_to_id`
                              AND us1.`role_id` !=2 #User the same Role could not access each other
                              OR EXISTS 
                               (
                                 -- The same Role will be allow if exist at custom share access
                                 SELECT 1 FROM `crm_user_param_modules_custom_accesses` mca1
                                 WHERE mca1.`module_id` = 1 AND mca1.`user_id` = 7 AND mca1.`role_id` = 2 LIMIT 1
                               ) GROUP BY upu1.`user1_id` LIMIT 1 
                             )

                           OR  EXISTS 
                             ( 
                                -- Only account created by current user
                                SELECT 1 FROM crm_user_param_users upu2 
                                INNER JOIN crm_users us2 ON upu2.`user1_id` = us2.`user_id`
                                WHERE upu2.`user_id` = 7 AND acc.`created_by` = upu2.`user1_id`
                                AND us2.`role_id` !=2  #User the same Role could not access each other
                                OR EXISTS 
                                (
                                  -- The same Role will be allow if exist at custom share access
                                  SELECT 1 FROM `crm_user_param_modules_custom_accesses` mca2
                                  WHERE mca2.`module_id` = 1 AND mca2.`user_id` = 7 AND mca2.`role_id` =2 LIMIT 1
                                ) GROUP BY upu2.`user1_id` LIMIT 1

                              )
                            OR  acc.`created_by` = 7

                        THEN 1
                        ELSE 0
                          END
                      )= 1, 1,0
                ) = 1

                OR 

                IF (
                      (
                          CASE asm.`assign_type` 
                        WHEN 
                         EXISTS 
                          ( 
                            SELECT 1 FROM `crm_user_param_groups` upg1
                            INNER JOIN crm_users us1 ON upg1.`user_id` = us1.`user_id`
                            WHERE upg1.`user_id` = 7 AND upg1.`group_id` = asm.`assign_to_id`
                             AND us1.`role_id` !=2 #User the same Role could not access each other

                             -- The same Role will be allow if exist at custom share access -- 
                             OR EXISTS 
                             (
                               SELECT 1 FROM `crm_user_param_modules_custom_accesses` mca1
                                WHERE mca1.`module_id` = 1 AND mca1.`user_id` = 7 AND mca1.`role_id` = 2 LIMIT 1
                             )  LIMIT 1
                          ) 

                          -- Only Account created by current user group --
                          OR EXISTS 
                          ( 
                            SELECT 1 FROM `crm_user_param_groups` upg2 
                             INNER JOIN crm_users us2 ON upg2.`user_id` = us2.`user_id`
                            WHERE upg2.`group_id` = asm.`assign_to_id` AND us2.`user_id` = acc.`created_by`
                             AND us2.`role_id` !=2 #User the same Role could not access each other
                             OR EXISTS 
                              (
                                -- The same Role will be allow if exist at custom share access --
                                SELECT 1 FROM `crm_user_param_modules_custom_accesses` mca2
                                WHERE mca2.`module_id` = 1 AND mca2.`user_id` = 7 AND mca2.`role_id` =2 LIMIT 1
                              )  LIMIT 1

                          )
                        OR acc.`created_by` = 7

                        THEN 2
                        ELSE 0
                          END
                      )= 2, 1,0
                ) = 1

Answer 1

There are lots of reasons you need to look at, and without more information this isn't going to be possible to diagnose. Here is a list of questions, in the order I'd suggest tackling them, to get you started:

First, is your query SARG-able? (https://en.wikipedia.org/wiki/Sargable) It seems pretty clear that it is not; given the nested sub-queries. if you can change that, it will be much faster. (See third question, perhaps?)

Second, can you structure this differently, so that you don't need nested sub-queries?

Third, are your sub-queries running the same thing each time? You may want to use temporary tables for this, so that the performance hit is only taken once, if you really need the nested sub-queries.

Fourth, are your tables indexed properly to optimize the query?

Lastly, you might try working with a DBA to actually investigate the query performance, and see if there are other things to do.