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:
- Performance through a better understanding of the optimizer
- 7.2.1. Optimizing queries with EXPLAIN
Dig Deeper on Linux servers
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes. Continue Reading
A MySQL expert describes two ways that the multi-master support can be used. Continue Reading
An expert describes where to find information on detection deadlock algorithms in MySQL. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.