Mysql Slow Log when using UNION ALL?

Question

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

• Vérace: Post the output of explain extended.
• ypercubeᵀᴹ: Is this part correct? NOT IN (SELECT user_from FROM users_block WHERE user_from='64500')
• ypercubeᵀᴹ: The gender != 'M' should be changed to gender = 'F', unless you are storing more than 2 genders there. It might help use of indexes.

Answer 1

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.

• user1179459: thanks, i heard that using counts in innodb can be costly, however do we have an alternative to get counts(*) in a highly efficient way ?
• Rick James: That comment comes from one fact: MyISAM keeps a count of the number of rows in the entire table; InnoDB does not. Any use of WHERE to filter the count defeats that on either engine. Your task has a WHERE, so the best optimization is to build an index that lets the work be done in the index instead of in the data.
• Rick James: The field names smell like stuff that is changing 'all the time', so the Data Warehouse trick of using 'Summary tables' probably does not apply.