How to optimize this complex query?

Question

How can I optimize this query? it is currently running in 0.0100 seconds.

SELECT comments.comment_content, comments.comment_votes, comments.comment_date,
  users.user_login, users.user_level, users.user_avatar_source, 
  groups.group_safename 
FROM comments
LEFT JOIN links ON comment_link_id=link_id 
LEFT JOIN users ON comment_user_id=user_id
LEFT JOIN groups ON comment_group_id=link_group_id 
WHERE comment_status='published' AND link_status='published' 
ORDER BY comment_id DESC

EXPLAIN output:

Indices:

Comment:

Users

Groups:

Answer 1

Twenty millisecond time is usually not considered slow. As some people note in the comments, you will need to redo the optimization when your tables become larger, because the MySQL optimizer (and optimizers for other RDMS) makes decisions based on the size of the index.

JOIN . , , :

FROM comments AS c
LEFT JOIN links AS L ON c.comment_link_id=L.link_id 
LEFT JOIN users AS u ON c.comment_user_id=u.user_id
LEFT JOIN groups AS g ON c.comment_group_id=g.link_group_id

, , . , - .

, JOIN ... ON NOT NULL? .

, groups: link_group_id group_safename. , (link_group_id,group_safename). , link_group_id.

users: user_id. , (user_id, user_login, user_level, user_avatar_source). , .

links: link_status link_id. LEFT JOIN JOIN, WHERE. link_status NOT NULL , , . (link_status, link_id).

comments: comment_status, . .

, OPTIMIZE LOCAL TABLE , EXPLAIN.