Page 1 of 1

crons

Posted: Mon Mar 27, 2006 3:09 am
by s.dot
I have a page which contains statistics from about 25 different mysql tables. each query pulls 20 records from each table, so its quite a bit of data/querying.

i currently have it set up to query all this data on every page load, which takes 3-4 seconds. this isn't good from a user perspective, or good on the server load.

so, i need to use a cronjob script to update the data, i understand that. my question comes in how/where to store the data?

do i need to make 25 more database tables? :? that seems like overkill for a simple task.

I could make 1 table with a column for each table, and implode() the results with a unique string and store it in the corresponding table field, then explode the data when the page is viewed.

or, i could not even go with the database and read a text file.

what is the practice for doing something like this?

Posted: Mon Mar 27, 2006 3:28 am
by feyd
is this data user specific? What's the required lifetime of the data?

Posted: Mon Mar 27, 2006 3:34 am
by s.dot
Ah. Good points.

The data is not user specific.. it's a statistical overview of the website sorted by category.
The lifetime is... neverending? The data is an aggregate total of since the website began up until page view (or last cronjob update)

Posted: Mon Mar 27, 2006 4:56 am
by jmut
scottayy wrote:Ah. Good points.

The data is not user specific.. it's a statistical overview of the website sorted by category.
The lifetime is... neverending? The data is an aggregate total of since the website began up until page view (or last cronjob update)
So this would mean data in the past do not change.

Here is a possible solution:

Central database that will store all required data for statistics.
Run once a cronjob to fill in data till yesterday. (for all times)
set up a Cronjob to run at night (after 00:00h) filling the table only for yesterday.
At each request execute queries only for current day (because it might be dynamic during day).

this way you get backup as well :lol:

P.S. http://pear.php.net/package/Cache althought not supported anymore I think this package is pretty decent (stable version)
And will be able to help you.

Posted: Tue Mar 28, 2006 5:45 pm
by s.dot
I think im just going to go with a table that has a field for each table in my database. Then run an hourly cron to get all data into an array, then serialize it, and store it in the db.