A faster way to combine two SQL table

Question

I would like to give it a try and ask about my codes on here from now on to see if there are improvement for'em

this code is supposed to combine two sql tables into one.

SELECT * FROM (SELECT * FROM posts WHERE id < $lastpost AND position = submitter order by id DESC LIMIT 5) t1 JOIN (SELECT username, firstname, lastname, avatar FROM users) t2 ON t1.submitter = t2.username

I was thinking that there might be a better way doing it using union or anything faster, if I'm even right about what I'm saying.

Answer 1

Assuming that each post has exactly one submitter, this is a more natural formulation of the query.

SELECT posts.*
     , users.username
     , users.firstname
     , users.lastname
     , users.avatar
    FROM posts
        JOIN users
            ON posts.submitter = users.username
    WHERE
        posts.id < $lastpost
        AND posts.position = posts.submitter
    ORDER BY posts.id DESC 
    LIMIT 5

However, its performance is likely to be similar to your original query. To investigate performance issues, run EXPLAIN SELECT to help you verify that the necessary indexes are in place.