index doesn't speed up

[mche...@gmail.com]

hi

   i have 20 millions rows , and this index doesn't speed up the query, please help:

create index mem on data(mem, sequence);

select * from data where mem is not null order by sequence

mem is varchar(200); and sequence is bigint

thanks

[Andreas Reichel]

Greetings!

As far as I remember, H2 considers composite indices only in certain situations -- but not for all possible optimisations.

Please EXPLAIN your query to check, if the index has been considered (I guess, it has not).

Maybe try again with 2 different indices, one for MEM and one for SEQUENCE (replacing you composite index).

Good luck

Andreas

--
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 h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d23b4ca9-2f9b-4bbe-84dd-b7d8469e129dn%40googlegroups.com.

[Andreas Reichel]

From https://www.h2database.com/html/performance.html:

This database uses indexes to improve the performance of SELECT, UPDATE, DELETE. If a column is used in the WHERE clause of a query, and if an index exists on this column, then the index can be used. Multi-column indexes are used if all or the first columns of the index are used. Both equality lookup and range scans are supported. Indexes are used to order result sets, but only if the condition uses the same index or no index at all. The results are sorted in memory if required. Indexes are created automatically for primary key and unique constraints. Indexes are also created for foreign key constraints, if required. For other columns, indexes need to be created manually using the CREATE INDEX statement.