I've been working on improving my website code. I've noticed that querying from a table inside a PHP while function, where while loop is also querying another table:

while($row = mysql_fetch_assoc($table1)){
$table2 = mysql_query(select);

It might be slowing down the performance, depending on the fact that we're querying the second table for each row of the first table.

So I've decided to select both of the tables from the same query. I came up with this code to accomplish what I think is more sufficient.

Now I have few questions:

  1. Am I correct for what I've said about the way my old code is slow?
  2. Would using PHP functions to query from each table individually be more sufficient?
  3. Is there anything that I need to add or change in my code, that I've made?


SELECT posts.*, users.username, SUM(IF(rates.hash = posts.hash, (rates.likes - rates.dislikes), 0)) AS rate 
FROM posts,users,rates WHERE posts.position = posts.submitter AND users.username = posts.submitter GROUP BY posts.hash ORDER BY posts.ID DESC LIMIT 5
  • \$\begingroup\$ Please include all of the code here. We cannot review code behind a link. \$\endgroup\$
    – Jamal
    Commented May 2, 2014 at 7:43
  • \$\begingroup\$ it's a demo link from sqlfiddle.com however, i've posted the code \$\endgroup\$ Commented May 2, 2014 at 7:47
  • \$\begingroup\$ So are you working with two tables or three ? Also, can you give the PHP code that creates select ? \$\endgroup\$
    – Zoyd
    Commented May 2, 2014 at 13:09

1 Answer 1


I don't really understand what this join is trying to accomplish? Seems redundant since the information is the same, but perhaps with your real world data it is not always the case.

WHERE posts.position = posts.submitter

Old-style JOINs are not recommended. Use INNER JOIN instead. This may also cause performance issues because rates is not explicitly joined so it would be trying to do a CROSS JOIN and those are very slow not to mention it can mess up your result set.

FROM    posts,
WHERE   posts.position = posts.submitter 
AND     users.username = posts.submitter


SELECT  posts.*, 
        IF(rates.hash = posts.hash, (rates.likes - rates.dislikes), 0)
        ) AS rate 
FROM    posts 
    ON users.username = posts.submitter
    ON users.username = rates.username
WHERE   posts.position = posts.submitter 
GROUP BY posts.hash 

I also formatted it for ease of reading. Executes in 2 ms using your data. Try it: http://sqlfiddle.com/#!2/d198f/4

Edit: fixed a typo.

  • \$\begingroup\$ I also see some potential changes to your DDL that could improve performance, but that would require a redesign of your tables. Let me know if you would like for me to make suggestions on that specifically and I can write another answer. \$\endgroup\$
    – Phrancis
    Commented May 19, 2014 at 21:21

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.