Selecting three tables with relationship

Question

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);
mysql_fetch_assoc($table2);
}

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?

Code:

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

• Jamal: Please include all of the code here. We cannot review code behind a link.
• Mustafa M Jalal: it's a demo link from sqlfiddle.com however, i've posted the code
• Zoyd: So are you working with two tables or three ? Also, can you give the PHP code that creates select ?

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,
 users,
 rates
WHERE posts.position = posts.submitter 
AND users.username = posts.submitter

Becomes:

SELECT posts.*, 
 users.username, 
 SUM(
 IF(rates.hash = posts.hash, (rates.likes - rates.dislikes), 0)
 ) AS rate 
FROM posts 
INNER JOIN users
 ON users.username = posts.submitter
INNER JOIN rates
 ON users.username = rates.username
WHERE posts.position = posts.submitter 
GROUP BY posts.hash 
ORDER BY posts.ID DESC LIMIT 5;

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.

• Phrancis: 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.