Before MySQL can do anything interesting (or useful) with a noncached query, it must parse the query into its component parts. As part of that process, it verifies that the query is syntactically valid and gathers some basic information about the query:
What type of query is this? Is it a SELECT, INSERT, UPDATE, or DELETE, or some other administrative command such as SET or GRANT?
Which tables are involved? Are there any aliases used?
What is the WHERE clause?
Are there other hints or modifiers involved?
To make good decisions, MySQL tries to answer several important questions.
Are there any indexes that are candidates for finding the rows quickly?
Which index is best? If multiple tables are involved, which index is best for each table?
Which tables depend on which other tables in the join?
What’s the optimal join order for the tables?
Of course, MySQL needs to make a decision very quickly and without actually testing all the options. Otherwise it might spend more time deciding how to execute the query than actually executing it!
The bulk of MySQL’s effort centers around indexes and table join order. These aren’t the only factors, but they’re certainly the important ones. To get a better understanding of what MySQL is thinking about a SELECT query, it’s best to look at the EXPLAIN output for the query.
