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.
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
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT posts.id, posts.text FROM posts LIMIT 0, 20