Re: Speed Up Code?
| From: | (Lutz Brückner) | Date: | Mon, 25 Mar 2002 23:47:03 +0000 |
| Subject: | Re: Speed Up Code? | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-17904@lists.php.net to get a copy of this message | ||
Hi Jeff,
the most important rule you should follow: don't query the database
in a loop to avoid a join! The following lines (maybe some changes
are necesary) will do the same job as your code, but considerable faster.
Especially if 'id' is an index in both tables.
Lutz
$sql = 'SELECT u.id, u.username, u.album_title,'
.' date_format(MAX(f.date), '%b. %D, %Y') as date1,'
.' COUNT(*) AS cnt'
.' FROM Chart_Users AS u'
.' INNER JOIN Chart_Files AS f USING (id)'
.' GROUP BY f.id'
.' ORDER BY u.album_title ASC';
$result = @mysql_query($sql,$connection) or die(" Couldn't execute query.");
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$username = $row['username'];
$title1 = $row['album_title'];
$title = stripslashes($title1);
$date1 = $row['date1'];
$display_block .= "<tr><td nowrap align=\"left\"><b><a
$href=\"display_album.php?id=$id\">$title </a></b></td><td
align=\"left\">$cnt</td><td align=\"right\"
nowrap>$date1</td></tr>";
}
jeff@webdesigns1.com (Jeff Oien) writes:
> Here is some code I have for an index page of people who post
> charts on the Web, kind of like Yahoo Photos or something.
> It displays the album title, number of images and date of last
> upload. The page takes about 5-6 seconds to load which is all
> in the queries I'm sure. Is there a way I can make this more efficient?
> http://www.webdesigns1.com/temp/code.txt
> Jeff
>
> --------------------------------------
>
> $table_name = "Chart_Users";
> $sql = "SELECT * FROM $table_name order by album_title";
> $result = @mysql_query($sql,$connection) or die(" Couldn't execute query.");
> //if(! $result = mysql_query($sql,$connection)) {
> // print("ERROR ".mysql_errno().":
> ".mysql_error()."<br>\n$sql<br>\n");
> // }
>
> while ($row = mysql_fetch_array($result)) {
> $id = $row['id'];
> $username = $row['username'];
> $title1 = $row['album_title'];
> $title = stripslashes($title1);
>
> $sql1 = "SELECT COUNT(*) FROM Chart_Files where id = '$id'";
> $result1 = @mysql_query($sql1,$connection) or die(" Couldn't execute query.");
> //if(! $result = mysql_query($sql1,$connection)) {
> //print("ERROR ".mysql_errno().":
> ".mysql_error()."<br>\n$sql<br>\n");
> //}
> $count = mysql_result($result1,0,"count(*)");
>
> $sql2 = "SELECT date_format(date, '%b. %D, %Y') as date1 FROM Chart_Files
> where
> id = '$id' order by photoid desc limit 1";
> $result2 = @mysql_query($sql2,$connection) or die(" Couldn't execute
> query.");
> $row = mysql_fetch_array($result2);
> $date1 = $row['date1'];
>
> if ($count > 0) {
>
> $display_block .= "<tr><td nowrap align=\"left\"><b><a
>
> href=\"display_album.php?id=$id\">$title </a></b></td><td
> align=\"left\">$count</td><td align=\"right\"
> nowrap>$date1</td></tr>";
> }
> }