SQL (HFSQL) : INNER JOIN not symetric in performances if WHERE clause?

Question

I have a basic HFSQL database, with 3 tables: Company, Person and Worker. Each table has ~5 columns, and 5000 entries. Worker has a foreign key to person called IDPerson, and a foreign key to entity called IDCompany.

I tested the 2 following queries (I only change the order of the INNER JOIN):

QUERY 1 (~5ms execution time)

SELECT *
FROM Person
INNER JOIN Worker ON Worker .IDPerson = Person.IDPerson
WHERE Worker.Company = 1;

QUERY 2 (~50ms execution time)

SELECT *
FROM Worker
INNER JOIN Person ON Worker .IDPerson = Person.IDPerson
WHERE Worker.Company = 1;

Number of entries found is the same, but execution time is 10x slower. Why? Isn't INNER JOIN supposed to be symmetric? Why the order changes the performance up to 10x when a WHERE clause is used?

Please help me understand this strange behaviour!

PS: performance issue is the same if I only select some columns instead of ""*

Answer 1

Even if you need all the columns, it is always a good idea to namely select them instead of using "*". The reason behind that is to to the database that you know what you want instead of collecting everything (even if you are collecting everything).

If you select only a few columns, you are telling the databse that you know what you want to extract hence it will only look for the specific columns instead of having a "behind the scene query" to get all the columns.

As for the performance difference between you two join, the database use heavy calculation to determine the best execution plan,it will look for indexes to use, the cache can also have so impact if you have run the query previously.

When testing do not hesitate to use something to clear the cache (it can have quite some impact)