Automatic SQL INNER JOIN optimization in HSQLDB

Question

The speed of the following queries is very different. The second ends an order of magnitude faster than the first.

SELECT * FROM A INNER JOIN B ON AX=BY WHERE BZ=1 SELECT * FROM A INNER JOIN (SELECT * FROM B) ON AX=BY WHERE BZ=1 

It would be great if someone writes why this is so. HSQLDB Database with JDBC .

Additional Information: HSQLDB Version - 2.3.2. And the AX column is indexed, but the BY column is not.

Answer 1

Answer: indexing

Imagine that I have a dictionary, and someone gives me the task of finding 5000 words in it. This task will take me several hours.
But now imagine that this dictionary is unsorted. It took me years to find all these words in it.
The computer is faster and for the first task it only needs milliseconds, and the second task remains a few seconds.

Why is the first request so slow?

This is because there is an INNER JOIN , and this is done on an unindexed column.

Why is the second request so fast?

This is because there is a subquery. This subquery materializes into a temporary table, and an index is created for the join column. This way, you are not joining an unindexed table B now, but with an indexed temporary table. HSQLDB creates this indexing in a temporary table to simplify the join. Even if you change the join condition to a more complex one (for example: AX = BY + 2*BZ ), this query will still be fast. This means that HSQLDB creates an index for the expression used in the join state.