SELF JOIN vs LEFT JOIN performance in FireBird

Question

To increase the speed of different queries run against a Firebird 2.5 database from a Delphi XE win32 application, which are the faster:

• self joins - for example

  SELECT e1.full_name AS Employee, e2.full_name AS Manager FROM employee e1 JOIN employee e2 ON e1.mng_id = e2.emp_no;

OR

• left joins with other table

  SELECT e1.full_name AS Employee, e2.full_name AS Manager FROM employee e1 LEFT JOIN employee_info e2 ON e1.mng_id = e2.emp_no;

taking in consideration that tables concerned will contain more than 50k rows, and I will add indexes in order to speed up the queries as much as I can.

Answer 1

to increase speed of a query, you need to create a index by all fields that are involved on WHERE clause