Help - need to quickly optimize a record count!

2005-07-06 Thread Brian Dunning 
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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2005-07-06 Thread SGreen 
Brian Dunning [EMAIL PROTECTED] 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

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

2005-07-06 Thread Peter 
Or even make .txt file with the cron and just include that txt file in 
your php

?
include_once('figures.txt');
?

Then in backgrond with perl(php) and cron you will udate that .txt file :-)

So it will be 1 quesry per 15 minutes :-)

Peter

[EMAIL PROTECTED] wrote:

Brian Dunning [EMAIL PROTECTED] 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



--
Best regards,

Peter

http://AboutSupport.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2005-07-06 Thread Gleb Paharenko 
Hello.



What engine do you use? 'SELECT COUNT(*) FROM TABLE_NAME' is 

very optimized, however only for MyISAM tables. Have you thought about

a dedicated table for such a purposes, or adding auto_increment column to

the table?







Brian Dunning [EMAIL PROTECTED] 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.

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]