MySQL Join and performance

Question

Assume this table: posts_groups

+---------+----------+--------+
| post_id | group_id | type   |    
+---------+----------+--------+
| 1       | 1        | public |    // group #1 sees post #1 where type is public
+---------+----------+--------+

And table: posts

+----+--------+
| id | type   |    
+----+--------+
| 1  | public |  // example : post #1 is public
+----+--------+

Query1

SELECT post_id FROM posts_groups pg
JOIN posts p ON p.id = pg.post_id
WHERE group_id = 1 AND pg.type = public

Well, this check group is 1 and type FROM CURRENT table, then select some post_id.

Query2

SELECT post_id FROM posts_groups pg 
JOIN posts p ON p.id = pg.post_id
WHERE group_id = 1 AND p.type = public

this query is also doing the same. but checking type FROM POSTS table.

But which method is faster and has better performance. any idea how these two queries work.

CONDITIONS PROCESS!

in query 1, it says if ( group_id = 1 AND p.type = public ) THEN if ( post.id = posts_groups.id ) JOIN posts

in query 2, it says if ( group_id = 1 ) THEN if ( post.id = posts_groups.id ) THEN if ( posts.type = public ) JOIN posts.

query2's process is a bit longer. or maybe my logic is wrong !!!

thanks in advance.

Answer 1

IMHO there could be a difference.

Of course logically the WHERE is processed after the JOIN, but most optimizers will apply the WHERE-condition before/during the join. If the type column is indexed the plan might change, so you should compare plans as echo-Me already wrote.

If both type columns should be public, you might help the optimizer by adding an addtional condition:

SELECT post_id FROM posts_groups pg
JOIN posts p ON p.id = pg.post_id
WHERE group_id = 1 AND pg.type = public 
AND p.type = public

or

SELECT post_id FROM posts_groups pg
JOIN posts p ON p.id = pg.post_id
AND pg.type = p.type
WHERE group_id = 1 AND pg.type = public

Answer 2

there is no difference in two queries , Just go ahead and try and use the one you like.

EDIT: as you edited your question.

They are joind before the WHERE clause , so no difference. to take condition from first or second table , because they joined first.