Mysql Slow Log when using UNION ALL? 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 &gt;= 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;
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.