[MySQL] Performance on repeated similar queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
lorenzo-s
Forum Commoner
Posts: 43
Joined: Tue Aug 25, 2009 12:25 pm

[MySQL] Performance on repeated similar queries

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: [MySQL] Performance on repeated similar queries

Post 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.
Post Reply