Hi Vladimir Rodionov, You are righ when explain the selectivity when we do not use index . But when use index, this query fetch, count with only 300 row, it take 10 second !
2014-06-03 11:35 GMT+07:00 Vladimir Rodionov <vladrodio...@gmail.com>: > 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. >> >> >
