UNION ALL vs CASE - SQL query optimization I need to retrieve the count of both approved and unapproved data from my_table.
And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion

DDL Query
CREATE TABLE my_table( id int(11) UNSIGNED NOT NULL,
 approved TINYINT(1) NOT NULL,
 invoice_no varchar(9) NOT NULL,
 deleted_at timestamp NULL DEFAULT NULL)

First Query
SELECT 
    COUNT(CASE WHEN approved=1 THEN id END) as approved,
    COUNT(CASE WHEN approved=0 and deleted_at is null THEN id END) as unapproved
    FROM my_table

Second Query
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=1 
 UNION ALL
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=0 and deleted_at is null

I have run command explain extended and attached the screenshot
Plan A -- no useful indexes:

Do the CASE approach.

Reason:  It is a single pass over the data.

PS:  SUM(approved = 1) is more concise, possibly faster.

Plan B -- useful indexes:

Do the UNION ALL approach if you have this composite index:

INDEX(approved, deleted_at)


and don't do COUNT(id), instead do COUNT(*).

That can be done in range scans on that one index.  The index is smaller than the table and each count only needs to scan part of the index.

Furthermore, UNION ALL is better optimized in 5.7 / 10.1.  So upgrade if you are not there yet.