Caching MySQL Results

Anon

I have a statistics section on every page of my website, these stats are grabbed from a MySQL database (about 8 calls). The problem is, every time a page is viewed, MySQL has to do those 8 calls, in addition to whatever the specific page requires. This is quite a performance hit... Is there a way I can store the results in a text file and have that file be updated automatically every, say, 2 minutes?

Anon

Run a php script from a cron job every 2 minutes if that's how often you need it to run, printing the results to a text file with all of the html formatting included.

Then, just include("results.txt") in your php script.

Anon

Riiight... how? 🙂

Anon

I'm pretty sure MySQL does some internal caching so even though you are making those same calls, the second, third, fourth times you call them etc etc etc they will return the results much much faster.

You can test this yourself by using the microtime() function and setting it when you start the query and end it, subtracting the difference and echoing it, you can see how long the queries took.

As for using crontab (you'll have to be using some sort of Unix flavor) type "man crontab" at your unix prompt for information on that.

You'll have to write the script to make the text file in Perl or maybe a shell script (or really any language which will let you interact with MySQL, say C or Python) unless you are running the CGI version of PHP.

Anon

Hi,

Kevin I am designing this very same thing!! lol, it's a web site that caches data and updates every 30mins or half hour.

First you need to research crontabs ; it's a unix/linux function to let a script activate on a specific time interval.

Second you need you do some research on file managment.

Example to get you mental juices flowing.
This is what i do in essence :

info is called from DATABASE ->
it is sorted and "printed" to an ARRAY ->
example :

<?PHP
$SOURCE = "SOME HTML; $SOURCE .= 'some more html';
echo $SOURCE;
?>

from this point the info can be ECHO ed or SAVED to a file.

PS : .= is a string concate. It ADDS to the string.

Good luck,
COPE