I have the following code:
select
*
from
table_1
join
table_2
on
table_1.col1 = table_2.col1
where
table_2.col2 = 1
This query works and give me the results that I expect. Now I would like to optimize this query. The idea is that I try to reduce the second query before the joining the two table. In other words, I suppose that "removing" rows and joining smaller tables should be faster that joining big tables and then selecting from them what I need. I implement my idea in the following way:
select
*
from
table_1
join
(
select
*
from
table_2
where
table_2.col2 = 1
)
on
table_1.col1 = table_2.col1
Surprisingly the second query is significantly slower than the first one. What am I doing wrong?
You can see difference in query execution plan.
Without plan i can only assume: In your first example, you have 2 tables. Mysql optimizer have some data statistic and can correctly choose and use index.
In your second query you don't have a table, only query result and optimizer haven't data statistic. May be in your case, optimizer execute query without index or something like this.
I think, subquery in your case, is bad practice. You have simple query, you must use your first example.