List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 17 2005 12:33am
Subject:Re: Database load and table design ?
View as plain text  
In the last episode (Apr 16), Michael Gale said:
> Currently I am using syslog-sql to store syslog data in a mysql
> database. The table format is something like:
> ID	hostname	facility	priority	date	message
> Now I am writting a perl app to calculate stats based on the data in
> the table which will get run everyday. It is currently doing a loop,
> here is an example:
> Select count(facility) from syslog WHERE machine = '$srv' AND facility = 
> '$fac' AND date1 > (NOW() - INTERVAL 24 hour)
> Now $srv is the name of the host and $fac is the facility name. This
> select statement is in a loop that loops through each server and each
> facility.
> When this is running it puts a load on the DB, since there could be
> about 20 host, each with 6 facilities, which equals about:
> 600 - The number of times that the select statement would be run ....
> I suppose that running the query:
> select host,facility from WHERE date1 > (NOW() - INTERVAL 24 hour)
> and letting perl do the math on the selected results would be less of a 
> system load ?

Why not 

SELECT host, facility, COUNT(*) FROM syslog 
 WHERE date1 > (NOW() - INTERVAL 24 HOUR) GROUP BY host, facility

	Dan Nelson
Database load and table design ?Michael Gale17 Apr
  • Re: Database load and table design ?Dan Nelson17 Apr