Query use PERSONWITHINDEX_MONTH_IDX instead of index_group_month_age. Do you mean query "SELECT MONTH, MAX(*AGE*)"?
On Wed, May 10, 2017 at 6:21 PM, Guillermo Ortiz <[email protected]> wrote: > Is there any limitation like databases which if it has to read more than > x% of data it do a full scan? > > I tried this: > > Result *SELECT MONTH, MAX(ID) FROM PERSONWITHINDEX GROUP BY MONTH * > September,1999868,February,1999869,March,1999871,August, > 1999844,June,1999867,April,1999854,July,1999866,January, > 1999857,May,1999862,November,1999830,October,1999864, > Elapsed time SELECT MONTH, MAX(ID) FROM PERSONWITHINDEX GROUP BY MONTH: > *2743ms* > [SELECT > __Z0.MONTH AS __C0_0, > MAX(__Z0.ID) AS __C0_1 > FROM "PersonWithIndex".PERSONWITHINDEX __Z0 > /* "PersonWithIndex".PERSONWITHINDEX_MONTH_IDX */ > GROUP BY __Z0.MONTH > /* group sorted */] > [SELECT > __C0_0 AS MONTH, > MAX(__C0_1) AS __C0_1 > FROM PUBLIC.__T0 > /* "PersonWithIndex"."merge_scan" */ > GROUP BY __C0_0] > > > ******Result *SELECT MONTH, MAX(ID) FROM PERSONWITHOUTINDEX GROUP BY > MONTH* September,1999860,November,1999870,February,1999869, > March,1999866,May,1999864,April,1999865,July,1999859, > August,1999871,January,1999867,June,1999868,October,1999854, > Elapsed time SELECT MONTH, MAX(ID) FROM PERSONWITHOUTINDEX GROUP BY MONTH: > *2418ms* > [SELECT > __Z0.MONTH AS __C0_0, > MAX(__Z0.ID) AS __C0_1 > FROM "PersonWithoutIndex".PERSONWITHOUTINDEX __Z0 > /* "PersonWithoutIndex".PERSONWITHOUTINDEX.__SCAN_ */ > GROUP BY __Z0.MONTH] > [SELECT > __C0_0 AS MONTH, > MAX(__C0_1) AS __C0_1 > FROM PUBLIC.__T0 > /* "PersonWithoutIndex"."merge_scan" */ > GROUP BY __C0_0] > > > I tried to execute many times same query and times are similar all the > times, I guess that indices aren't being useful. Am I doing something bad? > > public class PersonWithIndex implements Serializable { > @QuerySqlField(index = true) > public int id; > @QuerySqlField(index = true, orderedGroups = > {@QuerySqlField.Group(name="index_group_month_age", order=1)}) > public int age; > @QuerySqlField > public String dni; > @QuerySqlField(index = true, orderedGroups = > {@QuerySqlField.Group(name="index_group_month_age", order=0)}) > public String month; > > .... > > > > public class PersonWithoutIndex implements Serializable { > @QuerySqlField > public int id; > @QuerySqlField > public int age; > @QuerySqlField > public String dni; > @QuerySqlField > public String month; > > > 2017-05-08 12:32 GMT+02:00 Andrey Mashenkov <[email protected]>: > >> Group indices description can be found here [1]. Please, check if you >> read docs for your ignite version, as configuration for ignite 1.x and 2.0 >> can differs. >> Group indices in Ignite a similar to composite or multi-column indixes in >> databases. >> >> Yes, it is work that way. Group index for (groupFiled, timestampField) >> should be helpful. >> You can try to use EXPLAIN comand to check if correct index is in use. >> >> >> [1] https://apacheignite.readme.io/docs/indexes#section-group-indexes >> >> On Fri, May 5, 2017 at 8:17 PM, Guillermo Ortiz <[email protected]> >> wrote: >> >>> >>> >>> If I have two single indices to query: >>> >>> select * table a,b where a=1 and b=2 >>> >>> it doesn't work pretty good and I have to create a group index, how is >>> that possible? how does group indices work? >>> >>> Similar to this: >>> >>> select max(timestampField) >>> from myTable >>> group by groupField >>> >>> I have a index by groupField what it means that it should be really fast >>> to make the group and later make a partial map to find in each node the max >>> of the subset of data to merge the final submax and get the final result in >>> a "reduce" task after sending data through network. Is it how does it work? >>> This query it's really slow, I don't know if I created an index by >>> timestampField I would get better performance, but it doesn't too much >>> sense to create a index with a timestampField where there aren't too row >>> with the same value in a table with 250M rows.... >>> Any advice about this query? >>> >> >> >> >> -- >> Best regards, >> Andrey V. Mashenkov >> > > -- Best regards, Andrey V. Mashenkov
