Ask the Expert

MySQL's query optimizer

What is the role of the optimizer in MySQL?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: