You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Anil <an...@gmail.com> on 2017/04/04 09:18:34 UTC
Sort queries are slow
HI,
i have created a table with columns A and B. A is indexed column. and use
following queries
1. select * from Test where A = '<something>'
2. select * from Test where A = '<something>' order by B
#1 is fast as it uses default sorting of indexed column A. But #2 is slow.
Do you think creating index on B will speed up #2 query ? i tried that as
well and no luck.
are there any ways to improve the performance of #2 ? please advise.
Thanks
Re: Sort queries are slow
Posted by Anil <an...@gmail.com>.
Thanks Sergi.
#1 - we are displaying records in paginated way (using offset and rows) and
export feature whole data set.
waiting for ignite 2.0 :)
Thanks
On 4 April 2017 at 18:43, Sergi Vladykin <se...@gmail.com> wrote:
> Ok. Also I suspect that you have relatively large result sets, otherwise
> you would not notice any problems with sorting.
>
> I suggest you to do the following:
>
> 1. Return results by default with some reasonable LIMIT (30 or 50 for
> example) and have some separate button to get the whole result set if
> needed.
>
> 2. For the most frequently used filtering/sorting setups still create
> group indexes. I do not believe that your users will pick all the possible
> combinations with the same frequency.
>
> For Ignite 2.0 we already have some improvements in this area, but if the
> result set is huge enough, it will not help you as well.
>
> Sergi
>
> 2017-04-04 14:29 GMT+03:00 Anil <an...@gmail.com>:
>
>> Hi Sergi,
>>
>>
>> If you do not use indexes, then sorting will be performed each time.
>> Sorry.
>> * - i cannot use group indexes that you suggested. But i am using
>> individual indexes*
>>
>> From your pattern I suspect that you output the result set into some UI
>> table with sortable columns, am I right?
>> - *Yes* :)
>>
>> Thanks
>>
>> On 4 April 2017 at 16:45, Sergi Vladykin <se...@gmail.com>
>> wrote:
>>
>>> Alexey,
>>>
>>> Definitely! Please go ahead.
>>>
>>> Anil,
>>>
>>> If you do not use indexes, then sorting will be performed each time.
>>> Sorry.
>>>
>>> From your pattern I suspect that you output the result set into some UI
>>> table with sortable columns, am I right?
>>>
>>> Sergi
>>>
>>> 2017-04-04 13:54 GMT+03:00 Anil <an...@gmail.com>:
>>>
>>>> Hi Sergi,
>>>>
>>>> Thanks for the response.
>>>>
>>>> I have around 70 columns and support sorting on many columns. group
>>>> index is not suitable in my case. Do you have any other suggestions ?
>>>>
>>>> To some extent https://issues.apache.org/jira/browse/IGNITE-3013
>>>> improves the response time.
>>>>
>>>> Thanks
>>>>
>>>>
>>>> On 4 April 2017 at 15:28, Sergi Vladykin <se...@gmail.com>
>>>> wrote:
>>>>
>>>>> You should create a group index on (A, B) and rewrite the query the
>>>>> following way:
>>>>>
>>>>> select * from Test where A = '<something>' order by A, B
>>>>>
>>>>> Semantically it will be the same, but it will use index (A, B) for
>>>>> search and sorting.
>>>>>
>>>>> Sergi
>>>>>
>>>>> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>>>>>
>>>>>> HI,
>>>>>>
>>>>>> i have created a table with columns A and B. A is indexed column. and
>>>>>> use following queries
>>>>>>
>>>>>> 1. select * from Test where A = '<something>'
>>>>>> 2. select * from Test where A = '<something>' order by B
>>>>>>
>>>>>> #1 is fast as it uses default sorting of indexed column A. But #2 is
>>>>>> slow.
>>>>>>
>>>>>> Do you think creating index on B will speed up #2 query ? i tried
>>>>>> that as well and no luck.
>>>>>>
>>>>>> are there any ways to improve the performance of #2 ? please advise.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Sort queries are slow
Posted by Sergi Vladykin <se...@gmail.com>.
Ok. Also I suspect that you have relatively large result sets, otherwise
you would not notice any problems with sorting.
I suggest you to do the following:
1. Return results by default with some reasonable LIMIT (30 or 50 for
example) and have some separate button to get the whole result set if
needed.
2. For the most frequently used filtering/sorting setups still create group
indexes. I do not believe that your users will pick all the possible
combinations with the same frequency.
For Ignite 2.0 we already have some improvements in this area, but if the
result set is huge enough, it will not help you as well.
Sergi
2017-04-04 14:29 GMT+03:00 Anil <an...@gmail.com>:
> Hi Sergi,
>
>
> If you do not use indexes, then sorting will be performed each time.
> Sorry.
> * - i cannot use group indexes that you suggested. But i am using
> individual indexes*
>
> From your pattern I suspect that you output the result set into some UI
> table with sortable columns, am I right?
> - *Yes* :)
>
> Thanks
>
> On 4 April 2017 at 16:45, Sergi Vladykin <se...@gmail.com> wrote:
>
>> Alexey,
>>
>> Definitely! Please go ahead.
>>
>> Anil,
>>
>> If you do not use indexes, then sorting will be performed each time.
>> Sorry.
>>
>> From your pattern I suspect that you output the result set into some UI
>> table with sortable columns, am I right?
>>
>> Sergi
>>
>> 2017-04-04 13:54 GMT+03:00 Anil <an...@gmail.com>:
>>
>>> Hi Sergi,
>>>
>>> Thanks for the response.
>>>
>>> I have around 70 columns and support sorting on many columns. group
>>> index is not suitable in my case. Do you have any other suggestions ?
>>>
>>> To some extent https://issues.apache.org/jira/browse/IGNITE-3013
>>> improves the response time.
>>>
>>> Thanks
>>>
>>>
>>> On 4 April 2017 at 15:28, Sergi Vladykin <se...@gmail.com>
>>> wrote:
>>>
>>>> You should create a group index on (A, B) and rewrite the query the
>>>> following way:
>>>>
>>>> select * from Test where A = '<something>' order by A, B
>>>>
>>>> Semantically it will be the same, but it will use index (A, B) for
>>>> search and sorting.
>>>>
>>>> Sergi
>>>>
>>>> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>>>>
>>>>> HI,
>>>>>
>>>>> i have created a table with columns A and B. A is indexed column. and
>>>>> use following queries
>>>>>
>>>>> 1. select * from Test where A = '<something>'
>>>>> 2. select * from Test where A = '<something>' order by B
>>>>>
>>>>> #1 is fast as it uses default sorting of indexed column A. But #2 is
>>>>> slow.
>>>>>
>>>>> Do you think creating index on B will speed up #2 query ? i tried that
>>>>> as well and no luck.
>>>>>
>>>>> are there any ways to improve the performance of #2 ? please advise.
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>
>>>
>>
>
Re: Sort queries are slow
Posted by Anil <an...@gmail.com>.
Hi Sergi,
If you do not use indexes, then sorting will be performed each time. Sorry.
* - i cannot use group indexes that you suggested. But i am using
individual indexes*
From your pattern I suspect that you output the result set into some UI
table with sortable columns, am I right?
- *Yes* :)
Thanks
On 4 April 2017 at 16:45, Sergi Vladykin <se...@gmail.com> wrote:
> Alexey,
>
> Definitely! Please go ahead.
>
> Anil,
>
> If you do not use indexes, then sorting will be performed each time. Sorry.
>
> From your pattern I suspect that you output the result set into some UI
> table with sortable columns, am I right?
>
> Sergi
>
> 2017-04-04 13:54 GMT+03:00 Anil <an...@gmail.com>:
>
>> Hi Sergi,
>>
>> Thanks for the response.
>>
>> I have around 70 columns and support sorting on many columns. group index
>> is not suitable in my case. Do you have any other suggestions ?
>>
>> To some extent https://issues.apache.org/jira/browse/IGNITE-3013
>> improves the response time.
>>
>> Thanks
>>
>>
>> On 4 April 2017 at 15:28, Sergi Vladykin <se...@gmail.com>
>> wrote:
>>
>>> You should create a group index on (A, B) and rewrite the query the
>>> following way:
>>>
>>> select * from Test where A = '<something>' order by A, B
>>>
>>> Semantically it will be the same, but it will use index (A, B) for
>>> search and sorting.
>>>
>>> Sergi
>>>
>>> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>>>
>>>> HI,
>>>>
>>>> i have created a table with columns A and B. A is indexed column. and
>>>> use following queries
>>>>
>>>> 1. select * from Test where A = '<something>'
>>>> 2. select * from Test where A = '<something>' order by B
>>>>
>>>> #1 is fast as it uses default sorting of indexed column A. But #2 is
>>>> slow.
>>>>
>>>> Do you think creating index on B will speed up #2 query ? i tried that
>>>> as well and no luck.
>>>>
>>>> are there any ways to improve the performance of #2 ? please advise.
>>>>
>>>> Thanks
>>>>
>>>>
>>>
>>
>
Re: Sort queries are slow
Posted by Sergi Vladykin <se...@gmail.com>.
Alexey,
Definitely! Please go ahead.
Anil,
If you do not use indexes, then sorting will be performed each time. Sorry.
From your pattern I suspect that you output the result set into some UI
table with sortable columns, am I right?
Sergi
2017-04-04 13:54 GMT+03:00 Anil <an...@gmail.com>:
> Hi Sergi,
>
> Thanks for the response.
>
> I have around 70 columns and support sorting on many columns. group index
> is not suitable in my case. Do you have any other suggestions ?
>
> To some extent https://issues.apache.org/jira/browse/IGNITE-3013 improves
> the response time.
>
> Thanks
>
>
> On 4 April 2017 at 15:28, Sergi Vladykin <se...@gmail.com> wrote:
>
>> You should create a group index on (A, B) and rewrite the query the
>> following way:
>>
>> select * from Test where A = '<something>' order by A, B
>>
>> Semantically it will be the same, but it will use index (A, B) for search
>> and sorting.
>>
>> Sergi
>>
>> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>>
>>> HI,
>>>
>>> i have created a table with columns A and B. A is indexed column. and
>>> use following queries
>>>
>>> 1. select * from Test where A = '<something>'
>>> 2. select * from Test where A = '<something>' order by B
>>>
>>> #1 is fast as it uses default sorting of indexed column A. But #2 is
>>> slow.
>>>
>>> Do you think creating index on B will speed up #2 query ? i tried that
>>> as well and no luck.
>>>
>>> are there any ways to improve the performance of #2 ? please advise.
>>>
>>> Thanks
>>>
>>>
>>
>
Re: Sort queries are slow
Posted by Anil <an...@gmail.com>.
Hi Sergi,
Thanks for the response.
I have around 70 columns and support sorting on many columns. group index
is not suitable in my case. Do you have any other suggestions ?
To some extent https://issues.apache.org/jira/browse/IGNITE-3013 improves
the response time.
Thanks
On 4 April 2017 at 15:28, Sergi Vladykin <se...@gmail.com> wrote:
> You should create a group index on (A, B) and rewrite the query the
> following way:
>
> select * from Test where A = '<something>' order by A, B
>
> Semantically it will be the same, but it will use index (A, B) for search
> and sorting.
>
> Sergi
>
> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>
>> HI,
>>
>> i have created a table with columns A and B. A is indexed column. and use
>> following queries
>>
>> 1. select * from Test where A = '<something>'
>> 2. select * from Test where A = '<something>' order by B
>>
>> #1 is fast as it uses default sorting of indexed column A. But #2 is slow.
>>
>> Do you think creating index on B will speed up #2 query ? i tried that as
>> well and no luck.
>>
>> are there any ways to improve the performance of #2 ? please advise.
>>
>> Thanks
>>
>>
>
Re: Sort queries are slow
Posted by Alexey Kuznetsov <ak...@apache.org>.
Sergi,
May be it is worth to also add this advice on [1] ?
[1] https://apacheignite.readme.io/docs/indexes
On Tue, Apr 4, 2017 at 4:58 PM, Sergi Vladykin <se...@gmail.com>
wrote:
> You should create a group index on (A, B) and rewrite the query the
> following way:
>
> select * from Test where A = '<something>' order by A, B
>
> Semantically it will be the same, but it will use index (A, B) for search
> and sorting.
>
> Sergi
>
> 2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
>
>> HI,
>>
>> i have created a table with columns A and B. A is indexed column. and use
>> following queries
>>
>> 1. select * from Test where A = '<something>'
>> 2. select * from Test where A = '<something>' order by B
>>
>> #1 is fast as it uses default sorting of indexed column A. But #2 is slow.
>>
>> Do you think creating index on B will speed up #2 query ? i tried that as
>> well and no luck.
>>
>> are there any ways to improve the performance of #2 ? please advise.
>>
>> Thanks
>>
>>
>
--
Alexey Kuznetsov
Re: Sort queries are slow
Posted by Sergi Vladykin <se...@gmail.com>.
You should create a group index on (A, B) and rewrite the query the
following way:
select * from Test where A = '<something>' order by A, B
Semantically it will be the same, but it will use index (A, B) for search
and sorting.
Sergi
2017-04-04 12:18 GMT+03:00 Anil <an...@gmail.com>:
> HI,
>
> i have created a table with columns A and B. A is indexed column. and use
> following queries
>
> 1. select * from Test where A = '<something>'
> 2. select * from Test where A = '<something>' order by B
>
> #1 is fast as it uses default sorting of indexed column A. But #2 is slow.
>
> Do you think creating index on B will speed up #2 query ? i tried that as
> well and no luck.
>
> are there any ways to improve the performance of #2 ? please advise.
>
> Thanks
>
>