7
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ Why the sub-query, and why write a union-like query without the UNION keyword \$\endgroup\$ Apr 14, 2014 at 7:44
  • \$\begingroup\$ @JosephtheDreamer Code in the question should not be edited. If you feel that the code formatting needs work, you can point it out in an answer. (Example) \$\endgroup\$ Apr 14, 2014 at 9:11
  • \$\begingroup\$ @200_success Ok, noted :D \$\endgroup\$
    – Joseph
    Apr 14, 2014 at 13:43

1 Answer 1

4
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.