First query selectivity is 1:1000 = 0.1% Second is 300/1000 = 30% This pretty much explains performance difference.
Try : select count(*) from test where int_1 > 999 On Mon, Jun 2, 2014 at 9:20 PM, Pham Phuong Tu <tuphamphu...@gmail.com> wrote: > HI guys, > > I have a test table with 20M rows, one row have 2 column type integer, 1 > column have index, other dont't have. Value of integer column is random > value from 0 to 1000. > > Run query with equal condition below, query in column with index is much > faster. > "select count(*) from test where int_1 = 100;" > (0.084 seconds vs 15.221 seconds) => > 150x faster > > But when run a range query, query in column with index is not much faster. > "select count(*) from test where int_1 > 700;" > (7.852 seconds vs 12.889 seconds) => > 0.5x faster > > Should index have to optimize data organizaion or algorithm to increase > performance ? > > Thanks a lot. > >
