Thanks, Phonenix index lindex by the way create another table wich have same number of row with indexed table.
2014-06-03 12:04 GMT+07:00 Vladimir Rodionov <vladrodio...@gmail.com>: > 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. >>>> >>>> >>> >> >
