Geek asks and answers

MySQL joining and performance

Suppose this table: posts_groups

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

And the 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 control group is 1 and the 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 also does the same. but checking out the type FROM POSTS table .

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

PROCESS TERMS!

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 messages.

Process

query2 . , , .

.

+3
2

enter image description here

. , .

EDIT: .

WHERE, . , .

+4

.

WHERE JOIN, WHERE / . , , , echo-Me.

, , :

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


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
+1

More articles:


All Articles