Hi, Chrystophe! > But do you have a tweak in mind to get better response time on this kind of request ?
Do you mean queries that return an empty result set? Unfortunately, I'm not aware of ways of improving performance of such queries. Also, I don't know the task you're actually solving. Maybe it's possible to avoid such queries by replacing it with some different queries or logic, or data schema, if latency is critical. On Thu, Dec 16, 2021 at 5:26 PM Chrystophe Vergnaud < chrystophe.vergn...@gmail.com> wrote: > Hi Maxim, > > Thanks for the details. > > But do you have a tweak in mind to get better response time on this kind > of request ? > > BR, > > Chrystophe Vergnaud > Architect @ Cyblex Technologies > > > > Le jeu. 16 déc. 2021 à 15:09, Maksim Timonin <timoninma...@apache.org> a > écrit : > >> Hi, Chrystophe! >> >> Multifield index should perfectly work for cases with strict equality >> like: (a == ? && b == ?) OR (a == ? && b > ?) . But for queries with range >> queries for first field "a" ("a" > ? && b ...) you should not expect a >> boost of performance. You're right, it's due to B+Tree implementation - we >> store data in pairs (a, b). And then in the index storage the sequence will >> be: (1, 1), (1, 2), (1, 3), (2, 1). We sort by A, and only in case of >> equality field A, we check field B. >> >> So for queries like (a > 0 && b < 10) there is not much help from the >> condition on B for reducing data slice. Our implementation doesn't skip >> some sub-trees for conditions, but it checks tree range sequentially. >> >> > Worst, if the value of b is not present in the slice, it is responding >> as if the b was not in the WHERE clause at all (it seems to run a full scan >> on the sub-result) >> >> I think it can depend on the amount of data you return. If there is no >> data suitable for your condition, you will hang until the query finishes. >> But if you have some, it will return the cursor earlier, after preparing >> the first page for response, see SqlFieldsQuery.setPageSize(). >> >> Also, performance may depend on your index selectivity. >> >> >> On Thu, Dec 16, 2021 at 4:23 PM Chrystophe Vergnaud < >> chrystophe.vergn...@gmail.com> wrote: >> >>> Hello Stephen, >>> >>> I was created with SQL : >>> CREATE INDEX IF NOT EXISTS "t_idx_1" ON MYSCHEMA."t" ("a", "b", "c"); >>> >>> BR, >>> >>> Chrystophe Vergnaud >>> Architect @ Cyblex Technologies >>> >>> >>> Le jeu. 16 déc. 2021 à 13:51, Stephen Darlington < >>> stephen.darling...@gridgain.com> a écrit : >>> >>>> Can you show how you’ve defined your index(es)? >>>> >>>> > On 16 Dec 2021, at 12:27, Chrystophe Vergnaud < >>>> chrystophe.vergn...@gmail.com> wrote: >>>> > >>>> > Hello, >>>> > >>>> > I'm running an ignite 2.10 and I don't understand the behavior of the >>>> multi-columns index. >>>> > >>>> > For instance, I have a table t(id, a,b,c, d, e, f, g) >>>> > - id is a uuid and is the key >>>> > - a is a TIMESTAMP >>>> > - b is a SMALLINT >>>> > - c is a TINYINT >>>> > - e, f, g are VARCHAR >>>> > >>>> > this table have around 200M lines >>>> > >>>> > I have to select data based on a, b, c in this order, so basically, I >>>> have setup an index on (a,b,c) >>>> > >>>> > If I apply a select with a WHERE clause on "a>=x AND a < y", it works >>>> perfectly, the response time is ok (using USE INDEX) >>>> > >>>> > If I add the b in the WHERE clause I expect to optimize the response >>>> time but it is not the case. Worst, if the value of b is not present in the >>>> slice, it is responding as if the b was not in the WHERE clause at all (it >>>> seems to run a full scan on the sub-result) >>>> > >>>> > Do I miss something ? is it related to the implementation of the >>>> B+tree ? >>>> > >>>> > Thanks in advance for your help. >>>> > >>>> > Best regards, >>>> > >>>> > Chrystophe Vergnaud >>>> > Architect @ Cyblex Technologies >>>> >>>> >>>>