> > 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. >>> >> >> >
