Nope, your second query fetches 6M rows. 20M total rows in a table, one column is random int between 0 ..1000, you select all rows with column > 700 - 30% of a total which is 20M * 0.3 = 6M
On Mon, Jun 2, 2014 at 9:43 PM, Pham Phuong Tu <tuphamphu...@gmail.com> wrote: > 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. >>> >>> >> >
