Hi, Composite index field order matters. Try to set order=3 for "value" column.
On Tue, Aug 21, 2018 at 10:43 PM Ilya Kasnacheev <[email protected]> wrote: > Hello! > > Yes, I am afraid that you will need another index. > > Regards, > > -- > Ilya Kasnacheev > > 2018-08-21 19:55 GMT+03:00 Prasad Bhalerao <[email protected]>: > >> Hi, >> >> Thank you for pointing out the mistake. >> After changing the order to 1 as follows, SQL executed quickly. >> >> public class DnsNetBiosAssetGroupData implements >> Data<DefaultDataAffinityKey>,UpdatableData<DefaultDataAffinityKey> { >> >> @QuerySqlField >> private long id; >> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >> "dns_nb_asset_group_data_idx1", order = 2)}) >> private long assetGroupId; >> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >> "dns_nb_asset_group_data_idx1", order = 3)}) >> private int assetTypeInd; >> private int partitionId; >> @QuerySqlField >> private long subscriptionId; >> @QuerySqlField >> private long updatedDate; >> //@QuerySqlField (index = true) >> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >> "dns_nb_asset_group_data_idx1", order = 1)}) >> private String value; >> >> >> But now with this change I cannot use the same index for condition, "wh >> re assetGroupId=? and assetTypeInd=?" . Do I have to create separate >> group index on assetGroupId and assetTypeInd ? >> >> >> >> >> >> >> Thanks, >> Prasad >> >> On Tue, Aug 21, 2018 at 9:29 PM Ilya Kasnacheev < >> [email protected]> wrote: >> >>> Hello! >>> >>> I think that value should have order of 1 to be used here. Can you try >>> that? >>> >>> Regards, >>> >>> -- >>> Ilya Kasnacheev >>> >>> 2018-08-21 18:56 GMT+03:00 Prasad Bhalerao <[email protected] >>> >: >>> >>>> Original Sql query: >>>> >>>> SELECT dnsnb.value, >>>> dnsnb.id >>>> FROM DnsNetBiosAssetGroupData dnsnb >>>> JOIN TABLE (value VARCHAR = ? ) temp >>>> ON dnsnb.value = temp.value >>>> WHERE dnsnb.subscriptionId = ? >>>> AND dnsnb.assetGroupId = ? >>>> AND dnsnb.assetTypeInd = ? >>>> >>>> temp table list has around 1_00_000 values. >>>> >>>> I also tried changing the indexes as follows. But it did not work. >>>> >>>> public class DnsNetBiosAssetGroupData implements >>>> Data<DefaultDataAffinityKey>,UpdatableData<DefaultDataAffinityKey> { >>>> >>>> @QuerySqlField >>>> private long id; >>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >>>> "dns_nb_asset_group_data_idx1", order = 1)}) >>>> private long assetGroupId; >>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >>>> "dns_nb_asset_group_data_idx1", order = 2)}) >>>> private int assetTypeInd; >>>> private int partitionId; >>>> @QuerySqlField >>>> private long subscriptionId; >>>> @QuerySqlField >>>> private long updatedDate; >>>> //@QuerySqlField (index = true) >>>> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = >>>> "dns_nb_asset_group_data_idx1", order = 3)}) >>>> private String value; >>>> >>>> >>>> Thanks, >>>> Prasad >>>> >>>> On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev < >>>> [email protected]> wrote: >>>> >>>>> Hello! >>>>> >>>>> Can you please show the original query that you are running? >>>>> >>>>> Regards, >>>>> >>>>> >>>>> >>>>> -- >>>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >>>>> >>>> >>> > -- Best regards, Andrey V. Mashenkov
