Actually we allow to enforce join order in SQL (see [1]). I hope in the next release we will support index hints as well (they are already merged to H2 master).
[1] http://ignite.apache.org/releases/1.8.0/javadoc/org/apache/ignite/cache/query/SqlFieldsQuery.html#setEnforceJoinOrder(boolean) Sergi 2017-01-27 5:40 GMT+03:00 lawrencefinn <[email protected]>: > 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. >
