Hi, No, I'm sorry, I think there is currently no way to influence this.
Regards, Thomas On Monday, May 4, 2015, Peter <[email protected]> wrote: > I am having some problems with indices and order by in H2 1.3.176. I > managed to get it to work when I only query one table by: > * making sure the index has DESC specified as that is what I query on > * including the fields from the where clause even though it should be > unnecessary: > > explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 WHERE >> t166.OrderBookId = 'ABC' AND t166.MarketId = 123 AND >> t166.ExchangeCreationTimestamp >= '2015-04-01T00:00:00.000+02:00' AND >> t166.ExchangeCreationTimestamp <= '2015-05-04T23:59:59.999+02:00' ORDER BY >> t166.MarketId,t166.orderbookid,t166.ExchangeCreationTimestamp DESC; >> SELECT T166.TRADEID FROM PUBLIC.TRADES T166 >> /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP <= >> '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP >= >> '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = 'ABC' AND MARKETID = 123 >> */ >> /* scanCount: 1 */ >> WHERE (T166.EXCHANGECREATIONTIMESTAMP <= '2015-05-04T23:59:59.999+02:00') >> AND ((T166.EXCHANGECREATIONTIMESTAMP >= '2015-04-01T00:00:00.000+02:00') >> AND ((T166.ORDERBOOKID = 'ABC') AND (T166.MARKETID = 123))) >> ORDER BY =T166.MARKETID, =T166.ORDERBOOKID, >> =T166.EXCHANGECREATIONTIMESTAMP DESC >> LIMIT 11 >> /* index sorted */ >> /* >> total: 3 >> TRADES.FOO4 read: 3 (100%) >> */ > > > But when I join with another table H2 chooses to process the other table > first with the effect that the index cannot be used for sorting order (ie > no "index sorted"): > > explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 INNER JOIN >> INSTRUMENTS t167 ON t166.OrderBookId = t167.OrderBookId AND t166.MarketId = >> t167.MarketId WHERE t167.Currency_CPT_UPPER = 'SEK' AND >> t166.ExchangeCreationTimestamp >= '2015-04-01T00:00:00.000+02:00' AND >> t166.ExchangeCreationTimestamp <= '2015-05-04T23:59:59.999+02:00' ORDER BY >> t166.ExchangeCreationTimestamp DESC; > > SELECT T166.TRADEID > > FROM PUBLIC.INSTRUMENTS T167 > > /* PUBLIC.INSTRUMENTS_12: CURRENCY_CPT_UPPER = 'SEK' */ > > /* WHERE T167.CURRENCY_CPT_UPPER = 'SEK' */ > > /* scanCount: 2785 */ > > INNER JOIN PUBLIC.TRADES T166 > > /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP <= >> '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP >= >> '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = T167.ORDERBOOKID AND >> MARKETID = T167.MARKETID */ > > ON 1=1 > > /* scanCount: 296814 */ > > WHERE ((T166.EXCHANGECREATIONTIMESTAMP <= '2015-05-04T23:59:59.999+02:00') >> AND ((T167.CURRENCY_CPT_UPPER = 'SEK') AND (T166.EXCHANGECREATIONTIMESTAMP >> >= '2015-04-01T00:00:00.000+02:00'))) AND ((T166.ORDERBOOKID = >> T167.ORDERBOOKID) AND (T166.MARKETID = T167.MARKETID)) > > ORDER BY =T166.EXCHANGECREATIONTIMESTAMP DESC > > LIMIT 11 > > /* > > total: 301063 > > INSTRUMENTS.INSTRUMENTS_12 read: 30 (0%) > > INSTRUMENTS.INSTRUMENTS_DATA read: 1995 (0%) > > TRADES.FOO4 read: 6589 (2%) > > TRADES.TRADES_DATA read: 292449 (97%) > > */ > > > It does not matter if I include the join fields in the order by: > > explain analyze SELECT TOP 11 t166.tradeid FROM TRADES t166 INNER JOIN >> INSTRUMENTS t167 ON t166.OrderBookId = t167.OrderBookId AND t166.MarketId = >> t167.MarketId WHERE t167.Currency_CPT_UPPER = 'SEK' AND >> t166.ExchangeCreationTimestamp >= '2015-04-01T00:00:00.000+02:00' AND >> t166.ExchangeCreationTimestamp <= '2015-05-04T23:59:59.999+02:00' ORDER BY >> t166.MarketId,t166.orderbookid,t166.ExchangeCreationTimestamp DESC; >> SELECT T166.TRADEID >> FROM PUBLIC.INSTRUMENTS T167 >> /* PUBLIC.INSTRUMENTS_12: CURRENCY_CPT_UPPER = 'SEK' */ >> /* WHERE T167.CURRENCY_CPT_UPPER = 'SEK' */ >> /* scanCount: 2785 */ >> INNER JOIN PUBLIC.TRADES T166 >> /* PUBLIC.FOO4: EXCHANGECREATIONTIMESTAMP <= >> '2015-05-04T23:59:59.999+02:00' AND EXCHANGECREATIONTIMESTAMP >= >> '2015-04-01T00:00:00.000+02:00' AND ORDERBOOKID = T167.ORDERBOOKID AND >> MARKETID = T167.MARKETID */ >> ON 1=1 >> /* scanCount: 296814 */ >> WHERE ((T166.EXCHANGECREATIONTIMESTAMP <= >> '2015-05-04T23:59:59.999+02:00') AND ((T167.CURRENCY_CPT_UPPER = 'SEK') AND >> (T166.EXCHANGECREATIONTIMESTAMP >= '2015-04-01T00:00:00.000+02:00'))) AND >> ((T166.ORDERBOOKID = T167.ORDERBOOKID) AND (T166.MARKETID = T167.MARKETID)) >> ORDER BY =T166.MARKETID, =T166.ORDERBOOKID, >> =T166.EXCHANGECREATIONTIMESTAMP DESC >> LIMIT 11 >> /* >> total: 300673 >> INSTRUMENTS.INSTRUMENTS_12 read: 30 (0%) >> INSTRUMENTS.INSTRUMENTS_DATA read: 1995 (0%) >> TRADES.FOO4 read: 6592 (2%) >> TRADES.TRADES_DATA read: 292056 (97%) >> */ > > > Here are the relevant indices: > > CREATE INDEX PUBLIC.FOO4 ON PUBLIC.TRADES(MARKETID, ORDERBOOKID, >> EXCHANGECREATIONTIMESTAMP DESC) >> CREATE INDEX PUBLIC.INSTRUMENTS_12 ON >> PUBLIC.INSTRUMENTS(CURRENCY_CPT_UPPER) > > > Is there any way to use the index for order by even if the table with the > order by fields is not the one processed first by the join? Or > alternatively, is there a way to influence which table is processed first? > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
