Should you avoid home page accessing database?

Are database queries slowing home page too much?

[denisl]

My home page uses php to run three queries of the mysql database and this appears to slow the page up.

Is it considered bad practice to have your home page accessing a database for this reason or is it that my hosting company has a lot of databases on this server. There appears to be approaching 100 mysql databases in this server.

[dmorison]

Hi denisl,

The answer to your main question is a resounding "it depends" i'm afraid.

There are half-way-house alternatives to accessing a database for every home page view.

You could for example cache content in ready-prepared HTML that can be simply thrown out of the door when a request arrives. A background process can then rebuild the homepage from the database every few minutes or whatever.

This is how slashdot and the big news sites work (some of which can receives several thousand hits a minute sometimes). They certainly don't want to hit their database for every request!

It could be that your server is a bit of a hog. Three straight forward mySQL queries shouldn't be that noticeable.

Are you using indexes on your tables properly?

[denisl]

Thanks dmorrison

No, I'm probably not using indexing properly and as the database grows I need to deal with this. The others who have created databases on that server are probalby no better than I am!

I had also wondered if I need to learn how to create a static page from information gleaned via the database at regular intervals

[dmorison]

I had also wondered if I need to learn how to create a static page from information gleaned via the database at regular intervals

There are a few ways to do this. The most common is to have a script that is called at regular intervals via your servers "cron" process.

However, if you are on a hosting package and do not have the permissions to create cron jobs you can use PHP to look at a cache file and decide if it is recent enough to use, or whether it is time to build a new one.

Here's something to get you started - this is off the top of my head so syntax might not be quite right:

(/cache should be a directory that is writable by the server process above the www root)

<?php

$cachefile = "cache/index.php.inc";

$maxage = 900; // in seconds

$rebuild = False;

if fileexists($cachefile)
{
$fileinfo = fstat($cachefile);

if ($fileinfo["ctime"] + $maxage) < time()) $rebuild = True;
}
else
{
$rebuild = True;
}

if ($rebuild)
{
$html = "";

// now build your page into the string $html

$f = fopen($cachefile,"w");

fwrite($f,$html);

fclose($f);
}

require($cachefile);

?>

[edited by: dmorison at 12:30 pm (utc) on July 1, 2003]

[denisl]

Thank you once again dmorrison.

All I needed was a starting point.

[waldemar]

interesting, dmorrison...

as for my 2cents, I indeed changed the provider when I noticed the mysql database was really slow in comparison. I'm still wondering if there is a provider-review-site out there with "mysql benchmarks/tests"...

[denisl]

I'm suddenly panicing having paid my 200GBP to attempt to get into the Yahoo directory (thought it should be worth it) and I see someone from Y has accessed the site but not gone past the front page - I await an email.

As for hosting - I'm only paying 50GBP per year but close to bandwidth threshold where I shall have to pay more.
However I'm not keen to pay more if I simply stay on the same server with a lot of others.

On the otherhand, the step up to dedicated server is a bigger step than I was planning at present and the steps in between are not as clear as had originally thought.

[TheWebographer]

One thing that can help is to not close the database between queries...