How to speed up this slow query?

Question

I'm having some issue with this query that seems to be too slow...

SELECT SUM(c)  FROM (
    (
        SELECT COUNT( id ) AS c
        FROM QueueOne
        WHERE id = my_id
    )
        UNION ALL (
        SELECT COUNT( id ) AS c
        FROM QueueTwo
        WHERE id = my_id
    )
        UNION ALL (
        SELECT COUNT( id ) AS c
        FROM QueueThree
        WHERE id = my_id
    )
        UNION ALL (
        SELECT COUNT( id ) AS c
        FROM QueueFour
        WHERE id = my_id
        )
) AS d

It is actually quite simple : QueueOne, QueueTwo, QueueThree, QueueFour Are four queue with different type of column and unfortunately can not be squizzed to One column.

This query give us the number of all waiting queue from every Queue table. It seems that is too slow for mysql since it log it on the slow-query.log file

Any help would be appreciated.

EDIT Here's the explain :

+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | <derived2>        | ALL  | NULL          | NULL | NULL    | NULL |    4 |             | 
|  2 | DERIVED      | QueueOne          | ref  | ID            | ID   | 4       |      |    1 | Using index | 
|  3 | UNION        | QueueTwo          | ref  | ID            | ID   | 4       |      |    1 | Using index | 
|  4 | UNION        | QueueThree        | ref  | ID            | ID   | 4       |      |    1 | Using index | 
|  5 | UNION        | QueueFour         | ref  | ID            | ID   | 4       |      |    1 | Using index | 
| NULL | UNION RESULT | <union2,3,4,5>    | ALL  | NULL          | NULL | NULL    | NULL | NULL |             | 
+----+--------------+-------------------+------+---------------+------+---------+------+------+-------------+
6 rows in set (0.82 sec)

EDIT 2: A little more information, some tables have almost 15 000 000 records

Answer 1

Add an index on id, and rewrite into count(*)

SELECT SUM(c) 
FROM   ((SELECT COUNT(*) AS c 
         FROM   queueone 
         WHERE  id = my_id) 
        UNION ALL 
        (SELECT COUNT(*) AS c 
         FROM   queuetwo 
         WHERE  id = my_id) 
        UNION ALL 
        (SELECT COUNT(*) AS c 
         FROM   queuethree 
         WHERE  id = my_id) 
        UNION ALL 
        (SELECT COUNT(*) AS c 
         FROM   queuefour 
         WHERE  id = my_id)) AS d 

UPDATE

You should also look into parallelization and partitioning

Other benefits usually associated with partitioning include those in the following list. These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.

Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.

Answer 2

Make sure that there is an index for id in each table.

Use count(*) instead of count(id). The result is the same as there are no null-values in the id in the result, but it doesn't have to do the check for null values.