Why Secondary indexes is so slowly by my test?

[Chengying Fang <cy...@ngnsoft.com>]

Hi guys,


I found Secondary indexes too slowly in my product(amazon large instance) with cassandra, then I did test again as describe here. But the result is the same as product. What's wrong with cassandra or me?
Now my test:
newly installed ubuntu-12.04 LTS , apache-cassandra-1.1.6, default configure, just one keyspace(test) and one CF(TestIndex):

CREATE COLUMN FAMILY TestIndex  

    WITH comparator = UTF8Type  

    AND key_validation_class=UTF8Type  

    AND default_validation_class = UTF8Type  

    AND column_metadata = [  

        {column_name: tk, validation_class: UTF8Type, index_type: KEYS}  

        {column_name: from, validation_class: UTF8Type}  

        {column_name: to, validation_class: UTF8Type}  

        {column_name: tm, validation_class: UTF8Type}  

    ];

and 'tk' just three value:'A'(1000row),'B'(1000row),'X'(increment by test)
The test query from cql:
1,without index:select count(*) from TestIndex limit 1000000;
2,with index:select count(*) from TestIndex where tk='X' limit 1000000;
When I insert 60000 row 'X', the time:1s and 12s.
When 'X'  up to 130000,the time:2.3s and 33s.
When 'X'  up to 250000,the time:3.8s and 53s.


According to this, when 'X' up to billon, what's the result? Can Secondary indexes be used in product? I hope it's my mistake in doing this test.Can anyone give some tips about it?
Thanks in advance.
fancy

Re: Why Secondary indexes is so slowly by my test?

[Richard Low <rl...@acunu.com>]

Hi,

Secondary index lookups are more complicated than normal queries so will be
slower.  Items have to first be queried in the index, then retrieved from
their actual location.  Also, inserting into indexed CFs will be slower
(but will get substantially faster in 1.2 due to CASSANDRA-2897).

If you need to retrieve large amounts of data with your query, you would be
better off changing your data model to not use secondary indexes.

Richard.


On 7 December 2012 03:08, Chengying Fang <cy...@ngnsoft.com> wrote:

> Hi guys,
>
> I found Secondary indexes too slowly in my product(amazon large instance)
> with cassandra, then I did test again as describe here. But the result is
> the same as product. What's wrong with cassandra or me?
> Now my test:
> newly installed ubuntu-12.04 LTS , apache-cassandra-1.1.6, default
> configure, just one keyspace(test) and one CF(TestIndex):
>
>    1. CREATE COLUMN FAMILY TestIndex
>    2.     WITH comparator = UTF8Type
>    3.     AND key_validation_class=UTF8Type
>    4.     AND default_validation_class = UTF8Type
>    5.     AND column_metadata = [
>    6.
>            {column_name: tk, validation_class: UTF8Type, index_type: KEYS}
>    7.         {column_name: from, validation_class: UTF8Type}
>    8.         {column_name: to, validation_class: UTF8Type}
>    9.         {column_name: tm, validation_class: UTF8Type}
>    10.     ];
>
> and 'tk' just three value:'A'(1000row),'B'(1000row),'X'(increment by test)
> The test query from cql:
> 1,without index:select count(*) from TestIndex limit 1000000;
> 2,with index:select count(*) from TestIndex where tk='X' limit 1000000;
> When I insert 60000 row 'X', the time:1s and 12s.
> When 'X'  up to 130000,the time:2.3s and 33s.
> When 'X'  up to 250000,the time:3.8s and 53s.
>
> According to this, when 'X' up to billon, what's the result? Can Secondary
> indexes be used in product? I hope it's my mistake in doing this test.Can
> anyone give some tips about it?
> Thanks in advance.
> fancy
>



-- 
Richard Low
Acunu | http://www.acunu.com | @acunu