phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tao Xiao <xiaotao.cs....@gmail.com>
Subject Re: Can we create an secondary index for two or more columns?
Date Mon, 18 May 2015 06:12:12 GMT
Thank you Jesse, that really makes sense.

2015-05-18 11:19 GMT+08:00 Jesse Yates <jesse.k.yates@gmail.com>:

> 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 <xiaotao.cs.nju@gmail.com>
> 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 <byh0831@gmail.com>:
>>
>>> 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 <xiaotao.cs.nju@gmail.com>:
>>>
>>>> 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.
>>>>
>>>
>>>
>>
>

Mime
View raw message