process query not per visit, but for all visitors at certain intervals

[Calculus]

Hi, I have read the guidlines but this is a more general question so I didn't put in my technical info for the server.

 

Basically, I've got a query that fetches that last 150 or so entries in some table and for each hit on the PHP page the query gets executed, this is absolutely killing the server.

 

What I wanted to do is something like this:

 

Do not execute query for reach hit (don't be dependent on the PHP page being requested) but execute the query when x amount of time has passed since last execution. So if 100 people enter the page and the amount of time required for the results to be different hasn't arrived, all 100 will see the same content, but visitor 101 will see the refreshed page as the time for the query to update has come.

 

This will really, really, make the server completely relaxed, as this one and only query is taking about 60% of my RAM allocated for SQL, it's basically the whole functionality of my site, but I figured it doesn't have to get updated every second (I've got about 2 visits per second).

 

I have no idea how to do this, since the query is part of the PHP page and will get read each time a visitor enters the page.

 

Can you guys help?

Thanks.

[K_N]

I think what you want to look into is cacheing your page.

 

That will make it so users will load an HTML page with the output of the PHP script that's been run on it's own, and the PHP script with all that heavy database load will only be run if the html page is older than what you've set the limit to be, and once that script has been run, it will make a new copy of the html page, so the next user gets that instead.

 

I don't know how to do it off the top of my head, but hopefully that's what you're looking for.

[Calculus]

Hi, thanks for your reply.

 

I've had no choice but to Google around and take care of it very fast because my server was showing the infamous too many connections error.

 

What I've done is actually pretty similiar to what you've said, I made a cron that would generate that certain element of the page onc per 5 minutes, and the HTML output is included as a small HTML file.

 

The performance is definitely better now, but still I'm a bit worried too many includes (it's a small file though) aren't exactly the solution.

 

For now this should do, but this is definitely not the end of it, I have to find a better solition - perhaps caching like you said, anyone has a clue?

[K_N]

What exactly is your script doing? If you don't mind posting the source, or a reasonable stripped-down facsimile, some of us may be able to help you optimize it. (Though if you do, I would post it in the PHP Help forum, not this one)

[fenway]

Yup, this is caching -- no reason to post the script in the mysql forum.