SELECT * FROM customer JOIN orders USING (customerId) WHERE customerId = 42;
Is it better to fetch the list of customers, then for each one find the orders made by that customer? Or the other way around? The optimizer looks at statistics kept by the server about the tables, as well as the structure of the tables and their indexes, and chooses the best order to join the tables. In this case, assuming there is a primary key on customerId in the customer table, the optimizer will probably decide to fetch the one row for customer 42, and then find her orders.
The optimizer also decides on the source of the data. If an index exists on a selected field, the server could read the data from the index, or from the data file. Which to choose depends, among other things, on the percentage of the table to read -- reading the index is very quick for just a few rows, but much slower than going to the data file for 98% of the rows.
The design of each query should be considered with the query optimizer in mind. Check out these two links for further information about:
This was first published in July 2007