Shailendu, not always. Here's an example where subqueries is slower. In this example, the aggregates are 3x faster:
mysql> SELECT
-> SUM(LENGTH(city) < 5 ) AS Under5,
-> SUM(LENGTH(city) = 5 ) AS Len5,
-> SUM(LENGTH(city) = 6 ) AS Len6,
-> SUM(LENGTH(city) = 7 ) AS Len7,
-> SUM(LENGTH(city) = 8 ) AS Len8,
-> SUM(LENGTH(city) = 9 ) AS Len9,
-> SUM(LENGTH(city) = 10 ) AS Len10,
-> SUM(LENGTH(city) = 11 ) AS Len11,
-> SUM(LENGTH(city) = 12 ) AS Len12,
-> SUM(LENGTH(city) > 12 ) AS Over12
-> FROM Cities;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Under5 | Len5 | Len6 | Len7 | Len8 | Len9 | Len10 | Len11 | Len12 | Over12 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 229941 | 197486 | 290239 | 331789 | 326917 | 288873 | 240595 | 187475 | 142805 | 463234 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (2.19 sec)
# EXPLAIN:
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | Cities | index | NULL | city | 767 | NULL | 2686372 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
mysql> SELECT
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) < 5 ) AS Under5,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 5 ) AS Len5,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 6 ) AS Len6,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 7 ) AS Len7,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 8 ) AS Len8,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 9 ) AS Len9,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 10 ) AS Len10,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 11 ) AS Len11,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) = 12 ) AS Len12,
-> ( SELECT COUNT(*) FROM Cities WHERE LENGTH(city) > 12 ) AS Over12
-> ;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Under5 | Len5 | Len6 | Len7 | Len8 | Len9 | Len10 | Len11 | Len12 | Over12 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 229941 | 197486 | 290239 | 331789 | 326917 | 288873 | 240595 | 187475 | 142805 | 463234 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (6.44 sec)
# EXPLAIN:
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 11 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 10 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 9 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 8 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 7 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 6 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 5 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 4 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 3 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
| 2 | SUBQUERY | Cities | index | NULL | city | 767 | NULL | 2686372 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+--------------------------+
mysql> SHOW TABLE STATUS LIKE 'Cities'\G
*************************** 1. row ***************************
Name: cities
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2686372
Avg_row_length: 66
Data_length: 179077120
Max_data_length: 0
Index_length: 232341504
Data_free: 7340032
Auto_increment: 2699355
Create_time: 2014-03-08 16:19:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2097152000 |
+-------------------------+------------+
1 row in set (0.02 sec)
mysql>
Notes:
* There is a KEY(city). (But this is not critical to the argument.)
* All subqueries have to scan the entire index. (As opposed to being able to do some kind of 'range' scan.)
* The index is smaller than the buffer_pool. The timing difference would be _much_ worse otherwise.
* Yes, I did run each query twice -- to be sure that things were cached. The timings given reflect just CPU, no I/O.
In general, (and in my opinion), fetching a row is more costly than computing a function (or aggregate).
With subqueries, 10*2686372 rows were fetched, plus 10*2686372 LENGTHs and 1*2686372 COUNTs.
With aggregates, 1*2686372 rows were fetched, plus 10*2686372 LENGTHs and 10*2686372 SUMs.