It’s not radical to claim that one of the most important tasks of any DBMS query optimizer is to find the best join order for the tables participating in a query. Is it, e.g., better to read country or city first in
SELECT * FROM country JOIN city ON country.id=city.countryid WHERE city.population > 1000000 AND country.region="EMEA"
employee or department first in
SELECT * FROM employee JOIN department ON employee.dept_no=department.dept_no WHERE employee.first_name="John" AND employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01" AND department.location="Paris"
If the optimizer gets this …[Read more...]