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