1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Please include all of the code here. We cannot review code behind a link. \$\endgroup\$ – Jamal May 2 '14 at 7:43
  • \$\begingroup\$ it's a demo link from sqlfiddle.com however, i've posted the code \$\endgroup\$ – Mustafa M Jalal May 2 '14 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 May 2 '14 at 13:09
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\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 May 19 '14 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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