Hi Richard - like I said, whatever the merits of the situation,
that's the query that the ISP is not permitting. As soon as I change
that, they'll reactivate the account. The ISP is PowWeb if anyone
else wants to take it up with them; I've already talked myself red in
the face.
On Jul 6, 2005, at 5:51 PM, Richard Lynch wrote:
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
