RE: Re: Query performance varying with LIMIT keyword

[Alexandr Shapkin]

Hello,   Your query doesn’t use indices and performs a full scan on your tables. That requires more time in order to execute a join and get records.   Please, consider adding the corresponding index: https://www.gridgain.com/docs/latest/sql-reference/ddl#create-index   From: nunob Sent: Tuesday, January 21, 2020 7:17 PM To: user@ignite.apache.org Subject: Re: Query performance varying with LIMIT keyword   Hello,   Thanks for responding.   Well, it takes 50ms to fetch the first line.   What I don't get is why it takes 8 seconds to fetch the 50th line.   In both cases I'm only fetching ONE row.   I suppose query execution time should be roughly the same whether i want the 1st or the 50th line.   I mean, aren't OFFSET and LIMIT just discarding rows from the resulting dataset? Isn't it a question of  looping through the cursor until it finds the Nth row? Why does it look like it's responding with exponential time as I increase the offset?   Looking at explain it does look like it's doing a full table scan. In the following query I have indexes over the joining columns on both tables:   SELECT     SE__Z1.ISINCODE AS __C0_0,     M__Z0.SYMBOL AS __C0_1,     NULL AS __C0_2,     CURRENT_TIMESTAMP() AS __C0_3,     M__Z0.LASTTIME AS __C0_4,     M__Z0.OPENPRICE AS __C0_5,     M__Z0.HIGHPRICE AS __C0_6,     M__Z0.LOWPRICE AS __C0_7,     M__Z0.CLOSEPRICE AS __C0_8,     M__Z0.LASTPRICE AS __C0_9,     M__Z0.LASTQUANTITY AS __C0_10,     M__Z0.BIDPRICE AS __C0_11,     M__Z0.ASKPRICE AS __C0_12,     M__Z0.BIDSIZE AS __C0_13,     M__Z0.ASKSIZE AS __C0_14 FROM SERVERDEAL.MARKETDATA M__Z0     /* SERVERDEAL.MARKETDATA.__SCAN_ */ LEFT OUTER JOIN SIMS.SECURITYEXCHANGE SE__Z1     /* SIMS.SECURITYEXCHANGE.__SCAN_ */    ON (SE__Z1.SECURITYTYPE = M__Z0.MARKET)     AND ((SE__Z1.EXCHANGEID = M__Z0.EXCHANGE)     AND (SE__Z1.SYMBOL = M__Z0.SYMBOL)) LIMIT 11                         -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/