Q

MySQL's query optimizer

A MySQL expert describes the role of the query optimizer and provides an example of how it works.

What is the role of the optimizer in MySQL?
MySQL's query optimizer evaluates various possible ways for the server to execute a query. Consider a common query:
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

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close