-1

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 ""*

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Ricola3D
  • 2,402
  • 17
  • 16
  • Run the queries multiple times. Be sure the caches are cold. And check the execution plans. The most likely reason is that your database produces different execution plans. – Gordon Linoff Jun 10 '21 at 11:42
  • Look at the output of `EXPLAIN your_sql_query`, it will show you you where are the blockages. – Maxoholic Jun 10 '21 at 08:11

1 Answers1

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)

Karlheim
  • 121
  • 5
  • Thanks. Indeed in my code I'm using the column names, I just replaced it by "*" for the stackoverflow post for more simplicity :) – Ricola3D Jun 10 '21 at 08:26