Given the query below
SELECT * FROM tableA
LEFT JOIN tableB ON tableB.id = tableA.id
WHERE tableA.name = 'e'
.
tableA tableB
----------- -----------
id name id school
----------- -----------
1 a 1 AA
2 b 2 BB
3 c 3 CC
4 d 4 DD
5 e 5 EE
.
What baffles me is the process that is happening behind.
Does the system JOINs first both tableA and tableB before they select only the row from the WHERE clause?
If yes, then what about this query
SELECT * FROM
(
SELECT * FROM tableA
WHERE name = 'e'
) A LEFT JOIN tableB ON tableB.id = A.id
Does this query select first using the WHERE clause before it JOINs the other table?
Sorry I can't find the answer on the web. I wanted to lessen the MySQL resource bandwidth. Does the second query is the best for this situation?
explain
to see what MySQL does. And yes, in your case, it will first look in table A. Preferable with an index. Your second query should by identical to your first one, although in older mysql versions, it will create an additional derived table (which is slower).EXPLAIN
, we will help explain how it applies. (I agree thatEXPLAIN
is cryptic.)