Code:
MS SQL 2000
Rows with unpaid_flag = 'Y' is 1
Rows with unpaid_flag = 'N' is 98201
With Index
select * from orders
where unpaid_flag = 'Y'
Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
select * from orders
where unpaid_flag = 'N'
Table 'Orders'. Scan count 1, logical reads 289, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 929 ms.
Without index
select * from orders
where unpaid_flag = 'Y'
Table 'Orders'. Scan count 1, logical reads 141, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 38 ms.
select * from orders
where unpaid_flag = 'N'
Table 'Orders'. Scan count 1, logical reads 141, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 652 ms.
So it seems...
Searching 'Y' with index is faster
Searching 'N' without index is faster
Now, my original question was why searching 'N' without index is faster. I think it's because an index could only cover about 300 rows??
I was mistakenly thinking that when you indexed that column, you would get an index 'Y' which will cover ALL the 'Y's rows (1 row) and an index 'N' which will cover ALL the 'N's rows (98201 rows).