If we need to get high performance from your MySQL server, we need to understand MySQL query execution flow. After this, much of query optimization is a matter of reasoning from principles, and query optimization becomes a very logical process. What happens when we send MySQL a query:
- The client sends the SQL statement to the server.
- The server checks the query cache. If there's a hit, it returns the stored result from the cache; otherwise, it passes the SQL statement to the next step.
- The server parses (through the parser), preprocesses (through the preprocessor), and optimizes (through the query optimizer) the SQL into a query execution plan.
- The query execution engine executes the plan by making calls to the storage engine API.
- The server sends the result from the storage to the client.
The Query Cache
Before even parsing a query, MySQL checks for it in the query cache, if the cache is enabled. If MySQL find a match in the query cache, it will check privileges before returning the cached query. If the privileges are OK, MySQL retrieves the stored result from the query cache and sends it to the client, bypassing every other stage in query execution. The query is never parsed, optimized, or executed.
MySQL parser breaks the query into tokens and builds a "parse tree" from them. The parser uses MySQL's SQL grammar to interpret and validate the query.
MySQL preprocessor then checks the resulting parse tree for additional semantics that the parser can't resolve.After this, it also checks privileges. This is normally very fast unless your server has large numbers of privileges.
The query optimizer
The parse tree is now valid and ready for MySQL query optimizer to turn it into a query execution plan. A query can often be executed many different ways and produce the same result. The optimizer's job is to find the best option.