MySQL – Slow Log When Using UNION ALL

MySQLperformancequery-performance

I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ?
I have already used indexes in following in the user tables and other tables. however its still not right. All my tables are in InnoDB table types.

SELECT  count(*) as total
    FROM  users_interest
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  users_view
    WHERE  user_to='64500'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  mail_msg
    WHERE  user_to='64500'
      AND  folder='1'
      AND  new='Y'
    UNION  ALL 
SELECT  count(*)
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden' )
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  online=1
    UNION  ALL 
SELECT  count(*) as total_recent
    FROM  user
    WHERE  activated='Y'
      AND  mode NOT IN('suspended','hellban','errorban','deleted','hidden')
      AND  (gender!='M')
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_to='64500' )
      AND  user_id NOT IN (
        SELECT  user_from
            FROM  users_block
            WHERE  user_from='64500' )
      AND  last_visit >= DATE_SUB(NOW(), INTERVAL 60 MINUTE);

# Time: 160124 22:46:38
# User@Host: xyz_user[xyz_user] @ localhost []  Id: 194710
# Schema: xyz_dev  Last_errno: 0  Killed: 0
# Query_time: 10.199606  Lock_time: 0.000048  Rows_sent: 81  Rows_examined: 2358518  Rows_affected: 0
# Bytes_sent: 4158
SET timestamp=1453655798;

enter image description here

Best Answer

Please provide SHOW CREATE TABLE.

Indexes needed:

users_interest:  (user_to, new)
users_view:  (user_to, new)
mail_msg:  (user_to, folder, new)
users_block:  (user_from) and (user_to, user_from)
user:  (activated, online) and (activated, last_visit)

The construct NOT IN ( SELECT ... ) performs poorly; change it to a LEFT JOIN ... WHERE ... IS NULL. For example:

SELECT ... FROM user
    WHERE user_id NOT IN ( SELECT user_from
                         FROM users_block WHERE user_to=1 )
      AND other_stuff

-->

SELECT ... FROM user AS u
    LEFT JOIN user_from AS ub  ON u.user_id = ub.user_from
    WHERE ub.user_from IS NULL
      AND other_stuff

Or (I don't know whether this is better):

SELECT ... FROM user AS u
    WHERE NOT EXISTS ( SELECT * FROM users_block
                     WHERE user_to=1 AND users_from = u.user_id )
      AND other_stuff

A suggestion: Instead of having 5 rows of unlabeled counts, do something like

( SELECT 'hidden count' AS total, COUNT(*) AS ct ... )
UNION ALL
( SELECT 'blocked to  ' AS total, COUNT(*) AS ct ... )
...

so that each count is labeled.

Related Question