Re: [PHP] Help - need to quickly optimize a record count!
Brian Dunning wrote: 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: 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. Err, this is bubkis. We have 5 public servers on which there are thousands of sites from our users -- some are even *nuke, written in *very bad* PHP code, none of which are optimized. We never faced this issue of having to shut down a user because of a query such as yours. I'm not a database guru, but if you have an auto-incrementing primary key, can't you just do SELECT MAX(key) AS `num_records` FROM `terms`; Since you mentioned that records are never deleted, this could be a more resource-friendly way of doing it. I did a non-scientific test on one of our database tables that has 14,248 rows (its also never deleted, only added to). select count(*) as `count` from `log`; 0.3947s select max(id) as `count` from `log`; 0.2425s -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Help - need to quickly optimize a record count!
The key to this (no pun intended) is that without an index, MySQL needs to do a table scan to determine the number of rows in the table, and that will rob resources from the database server that everyone shares. My recommendation would be to create a artificial key as a new column in the table, make it your primary index and set it up with an auto increment key. Once you do this, your query should run like greased lightning, because, I believe, MySQL will use the index cardinality to determine number of rows, instead of counting them. Warren Vail -Original Message- From: Burhan Khalid [mailto:[EMAIL PROTECTED] Sent: Sunday, July 10, 2005 12:21 AM To: Brian Dunning Cc: php-general@lists.php.net; mysql@lists.mysql.com Subject: Re: [PHP] Help - need to quickly optimize a record count! Brian Dunning wrote: 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: 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. Err, this is bubkis. We have 5 public servers on which there are thousands of sites from our users -- some are even *nuke, written in *very bad* PHP code, none of which are optimized. We never faced this issue of having to shut down a user because of a query such as yours. I'm not a database guru, but if you have an auto-incrementing primary key, can't you just do SELECT MAX(key) AS `num_records` FROM `terms`; Since you mentioned that records are never deleted, this could be a more resource-friendly way of doing it. I did a non-scientific test on one of our database tables that has 14,248 rows (its also never deleted, only added to). select count(*) as `count` from `log`; 0.3947s select max(id) as `count` from `log`; 0.2425s -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Help - need to quickly optimize a record count!
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: 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. 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? Thanks in advance for any suggestions. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help - need to quickly optimize a record count!
It is indexed, and it's fast, but nevertheless my ISP won't allow it any longer. At least not as often as I need it. On Jul 6, 2005, at 2:10 PM, Philip Hallstrom wrote: If you have an index on the terms table that query should return almost instantly... Anyway, I'd start there first... On Wed, 6 Jul 2005, Brian Dunning wrote: 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: 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. 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? Thanks in advance for any suggestions. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Help - need to quickly optimize a record count!
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
Re: [PHP] Help - need to quickly optimize a record count!
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
