Thank you Jesse, that really makes sense. 2015-05-18 11:19 GMT+08:00 Jesse Yates <[email protected]>:
> create index my_idx on EXAMPLE (M.C0, M.C1) > > > This will create an index on both columns _at the same time_. This means > the row key in the index will be a combination of both columns. Creating an > index like this is only an advantage if you are commonly querying _both > columns_ at the same time. For instance, a query like: > > Select * from EXAMPLE WHERE m.c0 = "a" AND m.c1 = "b" will leverage the > index on both columns. However, if you are just querying each column > separately, then using your solution (b) will be better. > > Does that make sense? > > ------------------- > Jesse Yates > @jesse_yates > jyates.github.com > > On Sun, May 17, 2015 at 6:17 PM, Tao Xiao <[email protected]> > wrote: > >> Thanks to Yuhao, >> >> Is the column *M.C1* ignored when I invoke " create index idx on EXAMPLE >> (M.C0, M.C1) " ? >> >> Which of the following two solutions make sense? >> >> solution a): >> create index idx_1 on EXAMPLE (M.C0, M.C1) >> create index idx_2 on EXAMPLE (M.C1, M.C0) >> >> >> solution b): >> create index idx_1 on EXAMPLE (M.C0) >> create index idx_2 on EXAMPLE (M.C1) >> >> >> >> >> 2015-05-17 22:34 GMT+08:00 Yuhao Bi <[email protected]>: >> >>> Hi Xiao Tao, >>> >>> You can create another secondary index on the same columns. >>> create index another_index on EXAMPLE(M.C1, M.C0) >>> >>> After doing that, query b) should return faster. >>> >>> 2015-05-17 22:25 GMT+08:00 Tao Xiao <[email protected]>: >>> >>>> I tried to build secondary index for two columns, *M.C0* and *M.C1*: >>>> >>>> create index my_idx on EXAMPLE (M.C0, M.C1) >>>> >>>> >>>> Then I tried to query by two indexes, respectively: >>>> >>>> a). select M.C0 from EXAMPLE where M.C0 = 'c0_00000001' >>>> b). select M.C1 from EXAMPLE where M.C1 = 'c1_00000001' >>>> >>>> Query a) returned results in less than 0.3 second, and query b) >>>> returned results in about 13 seconds. It seems that index was built for >>>> column *M.C0*, not *M.C1*. >>>> >>>> Can we build secondary index for two or more columns ? >>>> >>>> Thanks. >>>> >>> >>> >> >
