MySQL Stored Procedure Programming

How MySQL Processes SQL

The following sections provide a brief overview of the parsing and caching steps that MySQL undertakes as it processes a SQL statement.

Parsing SQL

A SQL statement sent to the MySQL server must first be parsed. Parsing involves the following actions:

• Ensure that the SQL statement contains valid syntax.

• Check that that you have been granted appropriate access to the objects involved.

• Confirm that all required objects exist.

• Determine an execution plan for the SQL statement.

The execution plan represents MySQL’s strategy for retrieving or modifying the data specified by the SQL statement. The optimizer is that part of the MySQL code that is responsible for making these decisions. Here are some of the questions that the optimizer needs to ask before it can come up with its plan:

• Is there a way to rewrite the SQL so that it will execute more efficiently?

• Are there any indexes available to retrieve the required data?

• Will using these indexes improve performance? If so, which of the possible indexes should be used?

• If multiple tables are to be processed, in what order should the tables be processed?

Compared to some of the major relational databases (Oracle, SQL Server, DB2), MySQL’s optimizer might seem, at first glance, to be relatively simplistic. MySQL’s optimizer is, however, extremely effective. You will only rarely need to rewrite a SQL statement to make it perform more efficiently—the optimizer will usually make the right decision. Since the optimizer cannot create ...