Re: Help - need to quickly optimize a record count!

Brian Dunning <brian@briandunning.com> wrote on 07/06/2005 04:43:11 PM: > 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. > You could create a new table that you populate once with SELECT COUNT(*) then update that table every time your CRON job runs. That way you don't have to keep performing the COUNT() query when you could look up the value from a table. Shawn Green Database Administrator Unimin Corporation - Spruce Pine