count.sql

-- POSTGRES --

select count(*) from ad;
-- 445000
-- EXPLAIN
-- "Aggregate  (cost=11887.50..11887.51 rows=1 width=0)"
-- "  ->  Seq Scan on ad  (cost=0.00..10775.00 rows=445000 width=0)"


select count(*) from ad  where region_id_fk = 1988;
-- 30
-- 220ms
-- EXPLAIN
-- "Aggregate  (cost=11887.58..11887.59 rows=1 width=0)"
-- "  ->  Seq Scan on ad  (cost=0.00..11887.50 rows=32 width=0)"
-- "        Filter: (region_id_fk = 1988)"


select count(*) from (select * from ad where region_id_fk = 1988) ads
-- 30
-- 264ms
-- EXPLAIN
-- "Aggregate  (cost=11887.58..11887.59 rows=1 width=0)"
-- "  ->  Seq Scan on ad  (cost=0.00..11887.50 rows=32 width=0)"
-- "        Filter: (region_id_fk = 1988)"


-- MySQL --

use teste;

select * from ad limit 100;

select count(*) from ad;
-- 445000
-- 0.336s
-- EXPLAIN
-- id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
-- 1, SIMPLE, ad, index, , PRIMARY, 4, , 443311, Using index



select count(*) from ad  where region_id_fk = 1988;
-- 30
-- 0.351
-- EXPLAIN
-- # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
-- 1, SIMPLE, ad, ALL, , , , , 443311, Using where


select count(*) from (select * from ad where region_id_fk = 1988) ads;
-- 30
-- 0.971sec
-- EXPLAIN
-- id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
-- 1, PRIMARY, <derived2>, ALL, , , , , 443311, 
-- 2, DERIVED, ad, ALL, , , , , 443311, Using where

What happens is the second option generates a materialised view:

While the first: