Ignite Indexes

[Prasad Bhalerao <pr...@gmail.com>]

Hi,

I have gone through this link
<https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-group-indexes>,
but still have some doubts.

Please check the following example.

public class TestData
  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 1)})
  private long field1;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 4)})
  private int field2;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 2)})
  private int field3;

1) In this code I have created a group index on field1, field2 and field3.
field1 has order=1.
Does it mean that the rows will be sorted on first on *field1*(order=1) and
then *field3*(order=2) and then *field2*(order=4) ?
I am just trying to understand the significance of "ORDER".

2) Keeping the indexes as above, Will ignite be able to use the indexes in
following cases :
Please explain why if the ignite is not able to use the indexes.

I am trying to understand how ignite decides if it can use a index or not?

a) select * from TestData where field1 = ?

b) select * from  TestData where field1 = ? and field2 = ?

c) select * from  TestData where field2 = ?

d) select * from  TestData where field3 = ?

e) select * from  TestData where field2 = ? and field3 = ?

3) Should the columns in where clause be in order in which the group
indexes are defined to make use of indexes?


Prasad

Re: Ignite Indexes

["ilya.kasnacheev" <il...@gmail.com>]

Hello!

Regarding index order:

Index order basically means that records in the data_idx1 index will be
sorted by field1; if two records have the same field1 value, field3 will be
used as sorting tier, and if both field1 and field3 are same, field2 will be
used as sorting tier. Index is basically a sorted list of row ids with some
bells and whistles on top.

This means, you can look up data when you specify field1 (using binary
search). If you don't specify field1, you can't use this index. If you use
field3 also, you can look data up more precisely. Then you can also use
field2. You can't use field2 without field3 and either one without field1.

So in your case, a) and b) will benefit from having index data_idx1, but
neither one will use fields other than field1. All other statements won't
use this index at all.

Regards,



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/