this might help, scroll down to group indexes section:
https://ignite.apache.org/docs/latest/SQL/indexes
On Wed, Apr 20, 2022 at 10:59 AM Charlin S <charli...@hotelhub.com> wrote:
> Hi,
> why my query is having only one index field, it supposed to be two index
> column
>
> /* "TestModel".TESTMODEL_TESTFIELD3_ASC_IDX: TESTFIELD3 = 'EN' */
>
> Thanks & Regards,
> Charlin
>
>
> On Tue, 19 Apr 2022 at 19:34, Surinder Mehra <redni...@gmail.com> wrote:
>
>> I may be wrong but as an excercise, can you try group index on these
>> fields please and see if it makes any difference.
>> I would request Apache ignite Dev's to validate it.
>>
>> On Tue, 19 Apr 2022, 19:20 Charlin S, <charli...@hotelhub.com> wrote:
>>
>>> Hi,
>>> EXPLAIN SELECT
>>> TestField1,TestField2,TestField3,TestField4,TestField5
>>> FROM
>>> TestModel
>>> WHERE
>>> TestField2 = 'A02'
>>> AND
>>> TestField3 = 'EN'
>>>
>>> resulview [0]: SELECT
>>> __Z0.TESTFIELD1 AS __C0_0,
>>> __Z0.TESTFIELD2 AS __C0_1,
>>> __Z0.TESTFIELD3 AS __C0_2,
>>> __Z0.TESTFIELD4 AS __C0_3,
>>> __Z0.TESTFIELD5 AS __C0_4
>>> FROM "TestModel".TESTMODEL __Z0
>>> /* "TestModel".TESTMODEL_TESTFIELD3_ASC_IDX: TESTFIELD3 = 'EN' */
>>> WHERE (__Z0.TESTFIELD2 = 'A02')
>>> AND (__Z0.TESTFIELD3 = 'EN')
>>>
>>> resultview[1]: SELECT
>>> __C0_0 AS TESTFIELD1,
>>> __C0_1 AS TESTFIELD2,
>>> __C0_2 AS TESTFIELD3,
>>> __C0_3 AS TESTFIELD4,
>>> __C0_4 AS TESTFIELD5
>>> FROM PUBLIC.__T0
>>> /* "TestModel"."merge_scan" */
>>>
>>> Thanks & Regards,
>>> Charlin
>>>
>>> On Tue, 19 Apr 2022 at 18:04, Surinder Mehra <redni...@gmail.com> wrote:
>>>
>>>> Looks correct to me. Can you run explain plain for this query and see
>>>> if it uses index.
>>>>
>>>> On Tue, 19 Apr 2022, 17:41 Charlin S, <charli...@hotelhub.com> wrote:
>>>>
>>>>> Hi,
>>>>> My query details are
>>>>> fieldsQuery="SELECT
>>>>> TestField1,TestField2,TestField3,TestField4,TestField5
>>>>> FROM
>>>>> TestModel
>>>>> WHERE
>>>>> TestField2 = 'A02'
>>>>> AND
>>>>> TestField2 = 'EN'"
>>>>>
>>>>> //cache model
>>>>> public class TestModel : IBinarizable
>>>>> {
>>>>>
>>>>> [QuerySqlField(IsIndexed = true)]
>>>>> public string TestField1 { get; set; }
>>>>> [QuerySqlField(IsIndexed = true)]
>>>>> public string TestField2 { get; set; }
>>>>> [QuerySqlField(IsIndexed = true)]
>>>>> public string TestField3 { get; set; }
>>>>> [QuerySqlField]
>>>>> public string TestField4 { get; set; }
>>>>> [QuerySqlField]
>>>>> public decimal? TestField5 { get; set; }
>>>>>
>>>>> public void ReadBinary(IBinaryReader reader){//implementation}
>>>>> public void WriteBinary(IBinaryWriter writer){//implementation}
>>>>> }
>>>>> implementation
>>>>>
>>>>> SqlFieldsQuery fieldsQuery = new SqlFieldsQuery(query) { Timeout =
>>>>> TimeSpan.FromMilliseconds(10000) };
>>>>> List<T> list = new List<T>();
>>>>> // public ICache<string, T> IgniteCache { get; set; }
>>>>> IFieldsQueryCursor queryCursor =
>>>>> IgniteCache.Query(fieldsQuery);
>>>>>
>>>>> //our implementation
>>>>> queryCursor.Dispose();
>>>>>
>>>>> Thanks,
>>>>> Charlin
>>>>>
>>>>>
>>>>>
>>>>> On Mon, 18 Apr 2022 at 13:35, Surinder Mehra <redni...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Can you please show slow query console log output if it's using index
>>>>>> scan or full cache scan.
>>>>>> I ran into one scenario where index wasn't used and it ended up
>>>>>> scaning whole cache.
>>>>>> You can try this locally by using control centre and run explain query
>>>>>>
>>>>>> On Mon, 18 Apr 2022, 13:08 Charlin S, <charli...@hotelhub.com> wrote:
>>>>>>
>>>>>>> Hi Ignite team,
>>>>>>> We are using Ignite 2.10.0 with 4.6.2 and .Net 5 WebAPI and we have
>>>>>>> a 16-nodes(including 2 server nodes) Ignite cluster.
>>>>>>> We are facing slowness issues with some particular cache model query
>>>>>>> and other models query are fine.
>>>>>>>
>>>>>>> query type: SqlFieldsQuery
>>>>>>> Index: index created for where clause columns.
>>>>>>>
>>>>>>> Regards,
>>>>>>> Charlin
>>>>>>>
>>>>>>>
