Re: Database design for monitoring tool
Rick James Wrote:
-------------------------------------------------------
> > So a select query like the one above will not be
> able to complete within a couple of seconds?
> Think about it. If the data is 1GB; how long does
> it take to read a GB off disk?
Yeah I can imagine. If the data to read is a gig. However, in the case where half a gig has to be read from a database of multiple gigs, I hope the indexing greatly reduce the querytime.
>
> > dateTime BETWEEN "2012-04-20 00:00:00" AND
> "2012-04-30 00:00:00"
> Do you realize that that gives you 10 days plus
> one second?
Yes, I saw it in my graphs. Didn't bother to change it yet though
> Instead of doing
> (function = "CCH_ChallengeInsert" OR function =
> "DEP_10.51.172.214_1000" OR ...)
> you could express it this way:
> function IN ("CCH_ChallengeInsert",
> "DEP_10.51.172.214_1000", ...)
Ah, nice!
> Either of these may help, perhaps significantly:
> INDEX(server, dateTime)
> INDEX(server, function, dateTime)
> (I don't know which would be better.)
This is declared at the table creation, right? Not in the query.
> Still, that may not get it down to seconds.
>
> > GROUP BY HOUR(dateTime), DAY(dateTime),
> WEEK(dateTime), MONTH(dateTime),
> QUARTER(dateTime), YEAR(dateTime) ,function;
> This seems backward -- you will get all the
> midnights first; then all the 1am readings; etc.
> Consider this instead:
> GROUP BY LEFT(dateTime, 13), function
Okay, I should change the order then. The thing is, some queries will only group by month, so you would only get month(),quarter(),year().
> In the long run, you may need to store only hourly
> summaries, not individual 5-minute values.
Yeah, I think that would be better then. Since the queries are created dynamically anyway, it is just as easy to query the month-table if the query spans multiple months or the minute table if it only spans some hours.
Again, thanks for your help Rick. Much appreciated!