[Solved] When is JOIN executed in this case?

EverSQL Database Performance Knowledge Base

When is JOIN executed in this case?

Let's say I select data from table "posts":

id   |  bigint(20)   |  PK
text |  varchar(400)

and a table "likes":

user_id  |  bigint(20)
post_id  |  bigint(20)

those 2 fields are joined as primary key

Let's say my select on the table "posts" with 500'000 records is simply

SELECT id, text FROM posts LIMIT 0,20

and I need to know which posts have a like (there can only be one per user/post, see primary-key definition). So I could just do

SELECT id, text FROM posts LEFT JOIN likes ON posts.id = likes.post_id LIMIT 0,20

Will the query join on 20 records or on all 500'000 of table "posts"? No column of "likes" is used in any WHERE/GROUP clause in my effective query.

Output of 

EXPLAIN 
SELECT id
FROM posts
LEFT JOIN likes ON posts.id = likes.post_id
LIMIT 0 , 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  posts   index   NULL    PRIMARY 16  NULL    58  Using index
1   SIMPLE  likes   ref PRIMARY PRIMARY 8   legendaily.posts.id 1   Using index

Thanks

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 5): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
The optimized query:
SELECT
        posts.id,
        posts.text 
    FROM
        posts LIMIT 0,
        20

Related Articles



* original question posted on StackOverflow here.