H2 in-memory database query is very slow

Question

I am using H2 database to increase performance and therefore I decided to load data from MySQL to H2 database at run-time.

The scenario is that I created three tables as book, author, book_author. book_author is a many-to-many table.

The table book, author and book_author have records respectively 1000, 50000 and 50000.

The select query

select book.name 
from book, author , book_author 
where book.id = book_author.book_id 
and book_author.author_id = author.id 
and author.name = 'Charles Dickens'

Takes 7 minutes to execute.

I used spring-jdbc to create the H2 memory database.

EmbeddedDatabase database_01 = new EmbeddedDatabaseBuilder().
    setType(EmbeddedDatabaseType.H2).
    addScript("initial_script.sql").
    setName("database_01").build();

JdbcTemplate jdbcTemplate_01 = new JdbcTemplate(database_01);

Can somebody suggest why this much of time is taken and a way to optimize it ?

Answer 1

Did you create the right indexes? See also the documentation about index and how they are used in H2.