-- my table and indexes
create table "order"(
serial bigint,
id bigint primary key,
"accountId" bigint not null,
symbol varchar(8) not null,
side varchar(4) not null,
px decimal(18, 9),
qty decimal(18, 9),
"ordRejReason" varchar,
text varchar,
orderingpx decimal(18, 9) as (case side when 'Buy' then 10000000 - px
else px end)
);
create index orderbook_idx on "order"(symbol, side, orderingpx, serial);
create index account_symbol_idx on "order"("accountId", symbol);
testing data size 100k-1000k rows
-- explain show me this query use the index orderbook_idx, and it's fast
select * from "order" where symbol = ? and side = ? ;
-- explain show me this query use the index orderbook_idx also, buy it's
very slow,
-- even if the index contains the columns order by columns
select * from "order" where symbol = ? and side = ? order by orderingpx,
serial;
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/468cc564-7882-4485-81b7-01ef757cc52co%40googlegroups.com.
