Ľubomír Varga wrote:
> SELECT * FROM t_route
> WHERE t_route.route_type_fk = 1
> limit 4;
This one scanned the t_route table until it found four rows that
matched. It apparently didn't need to look at very many rows to find
the four matches, so it was fast.
> SELECT * FROM t_route
> WHERE t_route.route_type_fk =
> (SELECT id FROM t_route_type WHERE type = 2)
> limit 4;
This one came up with an id for a route type that didn't have any
matches in the t_route table, so it had to scan the entire t_route
table. (Based on your next query, the subquery probably returned
NULL, so there might be room for some optimization here.) If you had
chosen a route type with at least four matches near the start of the
route table, this query would have completed quickly.
> SELECT * FROM t_route, t_route_type
> WHERE t_route.route_type_fk = t_route_type.id
> AND type = 2
> limit 4;
Since it didn't find any t_route_type row which matched, it knew
there couldn't be any output from the JOIN, so it skipped the scan of
the t_route table entirely.
-Kevin