Page 1 of 1

[MySQL] Performance on repeated similar queries

Posted: Tue Sep 07, 2010 9:16 am
by lorenzo-s
I have a page with a list of comments sent by website users. During first tests I was displaying them with something like:

Code: Select all

$r = mysql_query("SELECT id, text, author FROM comments AS c");
while ($c = mysql_fetch_object($r)) {
    echo 'Text: ' . htmlentities($c->text;)
    $a = mysql_query("SELECT username FROM users WHERE id = {$c->author}"
    echo 'Author: ' . htmlentities(mysql_result($a, 0));
}
For a page with 50 comments, I perform 51 queries. Then, obviosly, I changed it in:

Code: Select all

$r = mysql_query("SELECT c.id, c.text, c.author, u.username FROM comments AS c INNER JOIN users AS u ON u.id = c.author");
while ($c = mysql_fetch_object($r)) {
    echo 'Text: ' . htmlentities($c->text;)
    echo 'Author: ' . htmlentities($c->username);
}
Only one query.

Tha fact is that I noticed that execution time is (exactly I think) the same! Do you know if MySQL do some optimization on accessing always the same table? Maybe it caches the tables index, I don't know... Tell me :D

Re: [MySQL] Performance on repeated similar queries

Posted: Tue Sep 07, 2010 9:50 am
by Eran
You didn't notice a difference in execution time since there is only one concurrent user (you) and the tables are probably very smaller. With a larger data set and more users loading it concurrently, the difference would be significant.