Correct, your query is not using any index because your WHERE clause(s) do not reference any indexed fields. In H2, if your query doesn't use an index it's fairly catastrophic for performance, when idexes are used it's very fast (Provided sufficient CACHE_SIZE). In this case, it looks like you should add an index on products_ort. After the table is established and before you run your queries, run ANALYZE; so it can calculate statistics on your data/indexes to enable it to choose the best index to use. Try your benchmarks again and see how they go. In order to verify / determine if your query is or is not using an index, precede your query with EXPLAIN and run it. If you see tableScan in the results, that means it's not using an index, it's reading the entire table - which is not ideal.
What we weren't able to tell from your post was 1.) How much data of what types are in the table - affecting the statistics and query plan 2.) How much memory the JVM was launched with, and what you've set (or not) the CACHE_SIZE to for H2 - which plays a huge role in performance - usually second only to not using any index. -Brian L -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/2IPrTth20uoJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
