Optimize mysql query with subqueries

Question

Below is MySQl query with which I am able to get disired result

But is there any way i could optimize the query

SELECT users.*,
    (SELECT country_name FROM country WHERE country_code = users.country_code)
        AS country_name,
    (SELECT zone_name FROM timezone WHERE timezone_id = users.timezone_id)
        AS zone_name,
    (SELECT GROUP_CONCAT(list_name)
     FROM list LEFT JOIN user_list ON user_list.list_id = list.list_id
     WHERE user_list.user_id = users.user_id AND user_list.status = "active")
        AS groups,
    (SELECT GROUP_CONCAT(promotion_name)
     FROM promotion LEFT JOIN promotion_user ON promotion_user.promotion_id = promotion.promotion_id
     WHERE promotion_user.user_id = users.user_id AND promotion_user.status = "active")
         AS promotions,
    (SELECT GROUP_CONCAT(full_name)
     FROM users u LEFT JOIN promotion_user ON promotion_user.promotor_id = u.user_id
     WHERE promotion_user.user_id = users.user_id AND promotion_user.status = "active")
         AS promotors
FROM users WHERE client_id = '2' AND status != 'deleted'
ORDER BY user_id desc
LIMIT 50 OFFSET 0

The Explain Output is

                                                possible        key   
id  select_type         table           type    _keys   key     _len    ref     rows    Extra

1   PRIMARY             users           index   NULL    PRIMARY 4       NULL    1045612 Using where
6   DEPENDENT SUBQUERY  promotion_user  ALL     NULL    NULL    NULL    NULL    16159   Using where
6   DEPENDENT SUBQUERY  u               eq_ref  PRIMARY PRIMARY 4       [1]     1       NULL
5   DEPENDENT SUBQUERY  promotion_user  ALL     NULL    NULL    NULL    NULL    16895   Using where
5   DEPENDENT SUBQUERY  promotion       ALL     PRIMARY NULL    NULL    NULL    4       Using where; Using join buffer (Block Nested Loop)
4   DEPENDENT SUBQUERY  list            ALL     PRIMARY NULL    NULL    NULL    1592    NULL
4   DEPENDENT SUBQUERY  user_list       ALL     NULL    NULL    NULL    NULL    159852  Using where; Using join buffer (Block Nested Loop)
3   DEPENDENT SUBQUERY  timezone        eq_ref  PRIMARY PRIMARY 4       [2]     1       NULL          
2   DEPENDENT SUBQUERY  country         ALL     NULL    NULL    NULL    NULL    239     Using where

                                                                        [1] test.promotion_user.promoter_id
                                                                        [2] test.promotion_user.promoter_id

Also show the DDL for the tables (or tell us which columns are indexed, what relations exist etc.) — hjpotter92
Can you post the table declares. Looking at the EXPLAIN output you have posted it looks like you have very few useful indexes on the tables. The lack of indexes is likely to slow things down dramatically. — Kickstart

Kickstart Kickstart · Accepted Answer · 2014-06-10T12:37:00

I would try using non correlated sub queries. However as you are only bringing back the details for a single user (hence a single row probably) this might not help. Beyond probably eliminating one sub query.

Something like this (untested as no data definitions or data examples)

SELECT `users`.*,
    country.country_name,
    timezone.zone_name,
    sub_groups.groups,
    sub_promotors.promotions,
    sub_promotors.promotors
FROM `users`
INNER JOIN country
ON country.country_code = users.country_code
INNER JOIN timezone
ON timezone.timezone_id = users.timezone_id
INNER JOIN 
(
    SELECT promotion_user.user_id, GROUP_CONCAT(full_name) AS promotors, GROUP_CONCAT(promotion_name) AS promotions
    FROM users u
    LEFT JOIN promotion_user ON promotion_user.promotor_id = u.user_id
    WHERE promotion_user.status = "active"
    GROUP BY promotion_user.user_id
) AS sub_promotors
ON sub_promotors.user_id = users.user_id
INNER JOIN 
(
    SELECT user_list.user_id, GROUP_CONCAT(list_name) AS groups
    FROM list
    LEFT JOIN user_list ON user_list.list_id = list.list_id
    WHERE user_list.status = "active"
    GROUP BY user_list.user_id
) AS sub_groups
ON sub_groups.user_id = users.user_id
WHERE users.client_id = '2'
AND users.status != 'deleted'
ORDER BY users.user_id 
DESC LIMIT 50 OFFSET 0

Correlated sub queries effectively forces MySQL to perform themselves once for each returned row. Changing these to non correlated sub queries which are joined means they can be performed once for all returned rows. Down side is that joining onto a sub query is poorly optimised as far as indexes in MySQL.

You might be able to remove the sub queries if the promotor full name, etc are unique.

Optimize mysql query with subqueries

1 Answers