Website too slow

**Ken Robinson** · Jul 18 '05, 11:45 AM

Re: Website too slow

bettina@coaster .ch wrote:[color=blue]
> I'm re-programming my Website (www.coaster.ch) in PHP and I find it too
> slow (although I have ADSL).[/color]

Speed of execution of PHP has nothing to do with the speed of your
connection. It has to do with the efficiency of your code and the speed
of the host.
[color=blue]
> That's more or less how it functions:
> Here my tables:
> 'COASTERS' (code of coaster, code of country, etc...)
> 'COUNTRIES' (code of country, names of countries in different
> languages, code of continent)
> 'CONTINENTS' (code of continent, names of continents in different
> languages)
>
> In my index.php (start site) I use a "while":
> I look for the first continent.
> I look for the first country
> I count all the coasters from 1st country
> echo country - number of coasters
> I look for the second country
> I count all the coasters from 2nd country
> ...
> ...[/color]

May we see you code. Without it, we will not be able to tell you how to
speed it up. It may be as simple as using a built in function instead
of a loop, or it could be a redesign of your logic.

Ken

**Chung Leong** · Jul 18 '05, 03:45 PM

Re: Website too slow

Try to minimize the number of queries per page. Data that is
static--names of countries, continents--information, doesn't have to
be in the database. Keep them in an array in your code instead.

**bettina@coaster.ch** · Jul 18 '05, 04:15 PM

Re: Website too slow

<body>
<?
include("connec tion.php");
include("banner .inc");
?>
<table class="main_tab le">
<tr>
<td height="30" width="20"></td>
<?
include("set_la nguage.php");
?>
<td></td>
<td align="right">
<a href="brewery_l ist.php?lang=<? echo $lang ?>"><? echo
$link_to_brewer ies ?></a>  
<a href="mailto:be ttina@coaster.c h?subject=Betti nas
Bierdeckel-Sammlung"><? echo $contact ?></a>  
<a href="sub_index .php?lang=<? echo $lang ?>"><? echo $info ?></a>
</td>
<td width="20"></td>
</tr>
</table>
<?
$count_coasters = mysql_query("SE LECT COUNT(*) FROM coasters");
$total_coasters = mysql_result($c ount_coasters,0 ,0);
$count_countrie s = mysql_query("SE LECT COUNT(*) FROM countries");
$total_countrie s = mysql_result($c ount_countries, 0,0);
$GET_DATE = mysql_query("SE LECT DATE_FORMAT(DAT E,'%d.%m.%Y') FROM
coasters ORDER BY DATE DESC LIMIT 1");
$update = mysql_result($G ET_DATE,0,0);
?>
<table class="main_tab le">
<tr class="blue_bar ">
<td colspan="5" height="1"></td>
</tr>
<tr>
<td height="30" width="20"></td>
<td align="left" class="heading" width="328"><? echo
$total_coasters _text ?> <? echo $total_coasters ?></td>
<td align="center" class="heading" width="328"><? echo
$total_countrie s_text ?> <? echo $total_countrie s ?></td>
<td align="right" class="heading" width="328"><? echo $update_text
?> <? echo $update ?></td>
<td width="20"></td>
</tr>
</table>
<table cellpadding="0" cellspacing="0" class="main_tab le">
<tr>
<td>
<?
$search_contine nts = mysql_query("SE LECT CONTINENT_CODE,
CONTINENT_$lang FROM continents ORDER BY CONTINENT_CODE ASC");
$i = 0;
while ($row = mysql_fetch_arr ay($search_cont inents, MYSQL_NUM)) {
$continents[$i][0] = $row[0];
$continents[$i][1] = $row[1];
$key_continent = $continents[$i][0];
?>
<table align="left" class="sub_tabl e_5">
<tr>
<td class="continen t" colspan="4"><? echo $continents[$i][1]
?></td>
</tr>
<?
$search_countri es = mysql_query("SE LECT COUNTRY_CODE,
COUNTRY_$lang FROM countries WHERE CONTINENT_CODE = $key_continent
ORDER BY COUNTRY_$lang ASC");
$j = 0;
while ($row = mysql_fetch_arr ay($search_coun tries, MYSQL_NUM)) {
$countries[$j][0] = $row[0];
$countries[$j][1] = $row[1];
$key_country = $countries[$j][0];
$count_coasters _country = mysql_query("SE LECT COUNT(*) FROM
coasters WHERE COUNTRY_CODE = '$key_country'" );
$total_coasters _country =
mysql_result($c ount_coasters_c ountry,0,0);
$flag = $key_country.". gif";
?>
<tr>
<td><img src="Flags/<? echo $flag ?>"></td>
<td><a href="breweries .php?lang=<? echo $lang ?>&code_country =<?
echo $countries[$j][0] ?>"><? echo $countries[$j][1] ?></a></td>
<td align="right" class="no_link" ><? echo
$total_coasters _country ?></td>
<td width="5"></td>
</tr>
<?
$j = $j + 1;
}
?>
</table>
<?
$i = $i + 1;
}
?>
</td>
</tr>
</table>

**bettina@coaster.ch** · Jul 18 '05, 04:15 PM

Re: Website too slow

I will consider this point that you mention. Thank you.
I can do that with the continents, but the countries may vary in the
future, so I would have to change the programm and the idea is that i
only change the database.

**Peter van Schie** · Jul 18 '05, 04:45 PM

Re: Website too slow

bettina@coaster .ch wrote:[color=blue]
> I will consider this point that you mention. Thank you.
> I can do that with the continents, but the countries may vary in the
> future, so I would have to change the programm and the idea is that i
> only change the database.
>[/color]

In that case it's still possible to reduce the queries. For example:
Only query the countries table once per session. Once you retrieved the
list of countries keep them in a session-var, for use in the other
subpages. Just an idea.

--

http://www.phpforums.nl

**Scott Auge** · Jul 21 '05, 06:55 AM

Re: Website too slow

In article <1121702243.985 842.317830@o13g 2000cwo.googleg roups.com>,
bettina@coaster .ch wrote:
[color=blue]
> <body>
> <?
> include("connec tion.php");
> include("banner .inc");
> ?>
> <table class="main_tab le">
> <tr>
> <td height="30" width="20"></td>
> <?
> include("set_la nguage.php");
> ?>
> <td></td>
> <td align="right">
> <a href="brewery_l ist.php?lang=<? echo $lang ?>"><? echo
> $link_to_brewer ies ?></a>  
> <a href="mailto:be ttina@coaster.c h?subject=Betti nas
> Bierdeckel-Sammlung"><? echo $contact ?></a>  
> <a href="sub_index .php?lang=<? echo $lang ?>"><? echo $info ?></a>
> </td>
> <td width="20"></td>
> </tr>
> </table>
> <?
> $count_coasters = mysql_query("SE LECT COUNT(*) FROM coasters");
> $total_coasters = mysql_result($c ount_coasters,0 ,0);
> $count_countrie s = mysql_query("SE LECT COUNT(*) FROM countries");
> $total_countrie s = mysql_result($c ount_countries, 0,0);[/color]

I would use the PHP date functions for this - no sense asking the
database for it... unless your DB is on another machine and IT'S date is
more important than the machine PHP is running on.

If you must, you should be able to do:

SELECT DATE_FORMAT(DAT E,'%d.%m.%Y')

without referencing a table and that other stuff.
[color=blue]
> $GET_DATE = mysql_query("SE LECT DATE_FORMAT(DAT E,'%d.%m.%Y') FROM
> coasters ORDER BY DATE DESC LIMIT 1");
> $update = mysql_result($G ET_DATE,0,0);
> ?>
> <table class="main_tab le">
> <tr class="blue_bar ">
> <td colspan="5" height="1"></td>
> </tr>
> <tr>
> <td height="30" width="20"></td>
> <td align="left" class="heading" width="328"><? echo
> $total_coasters _text ?> <? echo $total_coasters ?></td>
> <td align="center" class="heading" width="328"><? echo
> $total_countrie s_text ?> <? echo $total_countrie s ?></td>
> <td align="right" class="heading" width="328"><? echo $update_text
> ?> <? echo $update ?></td>
> <td width="20"></td>
> </tr>
> </table>
> <table cellpadding="0" cellspacing="0" class="main_tab le">
> <tr>
> <td>
> <?[/color]

Do you have an index on CONTINENT_CODE in your table definition?
[color=blue]
> $search_contine nts = mysql_query("SE LECT CONTINENT_CODE,
> CONTINENT_$lang FROM continents ORDER BY CONTINENT_CODE ASC");
> $i = 0;
> while ($row = mysql_fetch_arr ay($search_cont inents, MYSQL_NUM)) {
> $continents[$i][0] = $row[0];
> $continents[$i][1] = $row[1];
> $key_continent = $continents[$i][0];
> ?>
> <table align="left" class="sub_tabl e_5">
> <tr>
> <td class="continen t" colspan="4"><? echo $continents[$i][1]
> ?></td>
> </tr>
> <?[/color]

Index on CONTINENT_CODE, COUNTRY_... existing in the DB? I am beginning
to wonder if there is a better way to handle the lang aspect to this.
[color=blue]
> $search_countri es = mysql_query("SE LECT COUNTRY_CODE,
> COUNTRY_$lang FROM countries WHERE CONTINENT_CODE = $key_continent
> ORDER BY COUNTRY_$lang ASC");
> $j = 0;
> while ($row = mysql_fetch_arr ay($search_coun tries, MYSQL_NUM)) {
> $countries[$j][0] = $row[0];
> $countries[$j][1] = $row[1];
> $key_country = $countries[$j][0];
> $count_coasters _country = mysql_query("SE LECT COUNT(*) FROM
> coasters WHERE COUNTRY_CODE = '$key_country'" );
> $total_coasters _country =
> mysql_result($c ount_coasters_c ountry,0,0);
> $flag = $key_country.". gif";
> ?>
> <tr>
> <td><img src="Flags/<? echo $flag ?>"></td>
> <td><a href="breweries .php?lang=<? echo $lang ?>&code_country =<?
> echo $countries[$j][0] ?>"><? echo $countries[$j][1] ?></a></td>
> <td align="right" class="no_link" ><? echo
> $total_coasters _country ?></td>
> <td width="5"></td>
> </tr>
> <?
> $j = $j + 1;
> }
> ?>
> </table>
> <?
> $i = $i + 1;
> }
> ?>
> </td>
> </tr>
> </table>[/color]

**bettina@coaster.ch** · Jul 21 '05, 06:55 AM

Re: Website too slow

I really need the date of the last update from the database, not
today's date.
Now I've deleted the table with continents and included them in a
land.XX.inc... because the continents will not grown. But the problem
are the countries that they my grow. Somebody told me to put them in a
SESSION var, that would be really a SESSION Array... For example, I've
thought about the following:

$search_countri es = mysql_query("SE LECT COUNTRY_CODE,
COUNTRY_$lang FROM countries ORDER BY COUNTRY_CODE ASC");[color=blue]
> $i = 0;
> while ($row = mysql_fetch_arr ay($search_coun tries, MYSQL_NUM)) {
> $countries[$i][0] = $row[0];
> $countries[$i][1] = $row[1];[/color]
$_SESSION['countries'] = $countries;

but now I don't know how to reference the individual items of my
SESSION Array:
Like this my be?
$_SESSION['countries'] [ i ][0];

$_SESSION['countries'] [ i ][2]; etc, etc

**Peter van Schie** · Jul 21 '05, 06:55 AM

Re: Website too slow

bettina@coaster .ch wrote:
[color=blue]
> $search_countri es = mysql_query("SE LECT COUNTRY_CODE,
> COUNTRY_$lang FROM countries ORDER BY COUNTRY_CODE ASC");
>[color=green]
>> $i = 0;
>> while ($row = mysql_fetch_arr ay($search_coun tries, MYSQL_NUM)) {
>> $countries[$i][0] = $row[0];
>> $countries[$i][1] = $row[1];[/color]
>[/color]

Why not just:

$countries = array();
while ($row = mysql_fetch_arr ay($search_coun tries,
MYSQL_NUM)) {
array_push($cou ntries, $row);
}

$_SESSION['countries'] = $countries;

You'll get an array of arrays (== database rows) that way.
If you want to know what exactly is in your $_SESSION['countries'] array
use print_r($_SESSI ON['countries']) for debugging purposes. You'll know
how to reference the array elements then.

--

http://www.phpforums.nl

Website too slow

Website too slow

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment