How does MySQL handle WHERE tests?

Question

Hi,

As I was coding a php page building a MySQL request according to GET parameters, I was wondering if MySQL does any kind of reorganization of WHERE tests in a request, or if it just takes them as they come.

For example, if I execute this request:

SELECT * FROM `table` t WHERE (SELECT `some_value` FROM `another_table` u WHERE `t.id` = `u.id` LIMIT 1) = 10 AND `a_boolean` = 1;

Obviously the second test is faster and executing it first would filter a lot of entries, so there would be less subrequests to do for the other test. So, does MySQL reorganize the tests according to which one is faster/would filter the most entries, or does it just execute them in the given order?

Answer 1

Yes, MySQL (and any SQL server) makes a plan before getting to work, and tries to take the fastest approach possible. Explain gives some information on how MySQL handles the query.

Answer 2

You can use EXPLAIN (documentation) to find out details about any query.