ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lawrencefinn <lar...@gmail.com>
Subject Re: grouped index sort vs filter
Date Fri, 27 Jan 2017 02:40:41 GMT
Sorry for the delay, been working hard on this.  We have changed the data
model a lot but here it is from the git history:
Here is the class: LineItem - http://pastebin.com/hTuXa5E3 

The query is something like select li._val from lineitem li where member_id
= 123 order by member_id, name asc.

Our solution around this works but is a bit of a pain in the butt.  We
created "fake" boolean fields/keys for each of the composite keys with the
value of false.  So in this example we created a key called "f_name".  We
updated the composite key "member_name" to actually take in 3 fields -
member_id, f_name, and name.  Then our query becomes something like
select li._val from lineitem li where member_id = 123 and f_name = false
order by member_id, f_name, name

Including the fake field in the where clause forces the correct index to be
used.  We actually did this to support descending and ascending indexes for
the same fields too.

Here is an example of what it looks like in the end:
@QuerySqlField(index = true, name = "member_id", orderedGroups = {
            @QuerySqlField.Group(name = "member_name", order = 0),
            @QuerySqlField.Group(descending = true, name =
"member_name_desc", order = 0),
})
private Long memberId;

@QuerySqlField(index = true, orderedGroups = {
            @QuerySqlField.Group(name = MEMBER_NAME, order = 2),
            @QuerySqlField.Group(descending = true, name =
"member_name_desc", order = 2)
    })
    private String name;
    @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = MEMBER_NAME,
order = 1)})
    protected boolean f_name = false;
    @QuerySqlField(orderedGroups = {@QuerySqlField.Group(descending = true,
name = "member_name_desc", order = 1)})
    protected boolean f_name_desc = false;


I REALLY wish H2 had the ability to force join order or hint indexes.  It
looks like that is WAY out in their roadmap :(



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10277.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mime
View raw message