On Wed, July 6, 2005 1:43 pm, Brian Dunning said:
> I am cross-posting this to the PHP and the MySQL lists because I'm
> not sure in which technology my solution will lie.
>
> I have a pretty busy PHP/MySQL site that executes the following query
> a lot:
Define "a lot"
Every page hit?
> select count(*) as `count` from terms;
>
> My MySQL account was disabled by my ISP because this query was
> causing resource issues, so I need to find a different way to know
> the record count in that table. A few records are added once every 5
> minutes by a cron job. The record count is constant the rest of the
> time. No records are ever deleted.
A few records every 5 minutes is approximately one INSERT per minute.
That's *WAY* more resource-intensive than a hell of a lot of count(*)
queries...
Did your ISP specifically say it was that query, or is that your analysis
of what's causing the problem?
Cuz I'm suspecting that the count(*) might be a red herring you are
following.
Course, it could be a red herring handed to you *BY* the ISP. They may
have less experience with a high-volume site than you do. :-v
> Is it possible to create some kind of server-side variable, in which
> the cron job could store the record count, which would be accessible
> to all scripts, and would stay the same until it gets reset? Or is
> there a less-intense MySQL query I should be using instead?
Dumping it in a .txt file, as suggested, may move the SQL performance
problem to a hard drive performance problem...
Doing include('count.txt') as often as you had to be doing SQL count(*)
may cause disk trashing. Or not, depending on the disk cache and hardware
and other users and a few zillion other factors...
That may or may not get you into or out of more hot water with the ISP...
Another thing to MAYBE look into, would be shared memory, if that's in
your PHP... Not for the faint of heart, but at least you'd have the speed
you need.
You may just need to change hosts or upgrade your package.
There comes a point where your problem actually *IS* hardware, not software.
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
