Re: Database design for monitoring tool

[Ruben Werk]

Rick James Wrote:
-------------------------------------------------------
> Please mock up the SELECTs (based on any of these
> table designs). That will give us something more
> concrete to talk about. And it may lead to other
> benefits/flaws of one or more potential schema
> designs.

This is an example of one of the select queries. This is based on the current test database which has only one big table and no indexing. Each row in this table contains:
server, function, dateTime, calls, meanTime

SELECT SUM(calls) as yMeasure, dateTime, function, server
FROM realData
WHERE dateTime BETWEEN "2012-04-20 00:00:00" AND "2012-04-30 00:00:00" AND (function = "CCH_ChallengeInsert"
OR function = "DEP_10.51.172.214_1000"
OR function = "IAV_GenLogonChall"
OR function = "IAV_VerVerificationResp"
OR function = "QS_ContractsUserChanTask" )
AND (server = "s03apr0001" )

GROUP BY HOUR(dateTime), DAY(dateTime), WEEK(dateTime), MONTH(dateTime), QUARTER(dateTime), YEAR(dateTime) ,function;

> Keep in mind that 50M rows will be about 1GB,
> which will take several minutes to scan
> (especially unindexed).

So a select query like the one above will not be able to complete within a couple of seconds?

Thanks!