Multiple column filtering and sorting in Apache Ignite

classic Classic list List threaded Threaded
7 messages Options
mrinalkamboj mrinalkamboj
Reply | Threaded
Open this post in threaded view
|

Multiple column filtering and sorting in Apache Ignite

Following is my Poco class, my query include a set of filters on few columns, which is facilitated using the QuerySqlField attribute, further there's multiple column sorting, for which the fields are indexed, now my understanding is indexing has a role only in sorting, but in this case user can ask for Ascending or Descending and the combination of columns like "OrderId Asc,OrderName desc"

- What kind of index can facilitate this kind of query ?
- Can a field be indexed for both ascending and descending ?
- Are the group indexes required in this case  ?

In my view it might not be practical to create index for all the

public class OrderEntity
{
        [QuerySqlField(IsIndexed = true)]
        public int OrderId { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderName { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public DateTime OrderDateTime { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public double OrderValue { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderAddress { get; set; }        
}
Alexei Scherbakov Alexei Scherbakov
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite

Hi,

I suppose OrderId is unique field, right ?

If yes, what's the point in sorting on both OrderId and OrderName ?


2016-06-22 10:53 GMT+03:00 mrinalkamboj <[hidden email]>:
Following is my Poco class, my query include a set of filters on few columns,
which is facilitated using the QuerySqlField attribute, further there's
multiple column sorting, for which the fields are indexed, now my
understanding is indexing has a role only in sorting, but in this case user
can ask for Ascending or Descending and the combination of columns like
"OrderId Asc,OrderName desc"

- What kind of index can facilitate this kind of query ?
- Can a field be indexed for both ascending and descending ?
- Are the group indexes required in this case  ?

In my view it might not be practical to create index for all the

public class OrderEntity
{
        [QuerySqlField(IsIndexed = true)]
        public int OrderId { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderName { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public DateTime OrderDateTime { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public double OrderValue { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderAddress { get; set; }
}



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Multiple-column-filtering-and-sorting-in-Apache-Ignite-tp5783.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.



--

Best regards,
Alexei Scherbakov
ptupitsyn ptupitsyn
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite

Hi,

You can create as many indexes as you want, but it will consume more memory and hurt insert performance ([1], [2]).
As with any SQL db, you have to decide which kinds of queries need indexes more than others.

Pavel.

On Wed, Jun 22, 2016 at 11:42 AM, Alexei Scherbakov <[hidden email]> wrote:
Hi,

I suppose OrderId is unique field, right ?

If yes, what's the point in sorting on both OrderId and OrderName ?


2016-06-22 10:53 GMT+03:00 mrinalkamboj <[hidden email]>:
Following is my Poco class, my query include a set of filters on few columns,
which is facilitated using the QuerySqlField attribute, further there's
multiple column sorting, for which the fields are indexed, now my
understanding is indexing has a role only in sorting, but in this case user
can ask for Ascending or Descending and the combination of columns like
"OrderId Asc,OrderName desc"

- What kind of index can facilitate this kind of query ?
- Can a field be indexed for both ascending and descending ?
- Are the group indexes required in this case  ?

In my view it might not be practical to create index for all the

public class OrderEntity
{
        [QuerySqlField(IsIndexed = true)]
        public int OrderId { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderName { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public DateTime OrderDateTime { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public double OrderValue { get; set; }

        [QuerySqlField(IsIndexed = true)]
        public string OrderAddress { get; set; }
}



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Multiple-column-filtering-and-sorting-in-Apache-Ignite-tp5783.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.



--

Best regards,
Alexei Scherbakov

mrinalkamboj mrinalkamboj
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite

In reply to this post by Alexei Scherbakov
Hello Alexei,

This operation is via user input, we don't directly control it. It may not be efficient, but still user can do the operation.

Thanks,

Mrinal
mrinalkamboj mrinalkamboj
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite

In reply to this post by ptupitsyn
Hello Pavel,

I understand the issue related to over indexing, but my question was if I want to index same field for bi-directional sort, Ascending and Descending direction, then what are my options.

Looks like, I can apply a Group Index with a single field, original index for Ascending and group index for descending or vice versa, is there a better mechanism ?

Thanks,

Mrinal
Alexei Scherbakov Alexei Scherbakov
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite

In reply to this post by mrinalkamboj
Mrinal,

You must create group index for query like: 
ordery by field1 asc, field2 desc

You can define such index using annotations or QueryEntry [1]

I prefer QueryEntry for defining complex indexes.

Don't forget to check actual index usage by issuing EXPLAIN command from the H2 console or code.




2016-06-22 13:08 GMT+03:00 mrinalkamboj <[hidden email]>:
Hello Alexei,

This operation is via user input, we don't directly control it. It may not
be efficient, but still user can do the operation.

Thanks,

Mrinal



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Multiple-column-filtering-and-sorting-in-Apache-Ignite-tp5783p5788.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.



--

Best regards,
Alexei Scherbakov
Pavel Tupitsyn Pavel Tupitsyn
Reply | Threaded
Open this post in threaded view
|

Re: Multiple column filtering and sorting in Apache Ignite


On Wed, Jun 22, 2016 at 1:55 PM, Alexei Scherbakov <[hidden email]> wrote:
Mrinal,

You must create group index for query like: 
ordery by field1 asc, field2 desc

You can define such index using annotations or QueryEntry [1]

I prefer QueryEntry for defining complex indexes.

Don't forget to check actual index usage by issuing EXPLAIN command from the H2 console or code.




2016-06-22 13:08 GMT+03:00 mrinalkamboj <[hidden email]>:
Hello Alexei,

This operation is via user input, we don't directly control it. It may not
be efficient, but still user can do the operation.

Thanks,

Mrinal



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Multiple-column-filtering-and-sorting-in-Apache-Ignite-tp5783p5788.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.



--

Best regards,
Alexei Scherbakov