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]. 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.
