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:phplist@xxxxxxxxxxxx] > Sent: Sunday, July 10, 2005 12:21 AM > To: Brian Dunning > Cc: php-general@xxxxxxxxxxxxx; mysql@xxxxxxxxxxxxxxx > Subject: Re: 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
