Why an union is faster than a group by
sql
Solution
Your query 2 is actually a nice trick. It works like this: You have an index on gender. The DBMS can seek into that index two times to get two ranges of rows (one for M and one for F). It doesn't need to read anything from these rows, just that they exist. It can count the number of rows that exist in the two ranges.
In the first query the DBMS needs to decode the rows to read the gender, then it needs to either sort the rows or build a hashtable to aggregate them. That is more expensive than just counting rows.
Problem
Well, maybe I am too old school and I would like to understand the following. query 1. ``` select count(*), gender from customer group by gender ``` query 2. ``` select count(*), 'M' from customer where gender ='M' union select count(*), 'F' from customer where gender ='F' ``` the 1st query is simpler, but for some reason in the profiler,when I execute both at the same time, it says that query 2 uses 39% of the time, and query 1, 61%. I would like to understand the reason, maybe I have to rewrite all my queries.