Don't forget to run an analyze to adjust the statistics for the
optimizer/indexes. Also, after any updates (on dynamic tables which yours is)
or any deletes run an optimize.
Quoting Paul Halliday <paul.halliday@stripped>:
> I am working on a database that deals with network statistics. I have
> a program that generates web reports based on this data every ten
> The table layout looks something like this:
> CREATE TABLE traffic
> unix_secs INT UNSIGNED NOT NULL,
> dpkts INT UNSIGNED NOT NULL DEFAULT 0,
> doctets INT UNSIGNED NOT NULL DEFAULT 0,
> first INT UNSIGNED,
> last INT UNSIGNED,
> srcaddr VARCHAR(15),
> dstaddr VARCHAR(15),
> srcport SMALLINT UNSIGNED,
> dstport SMALLINT UNSIGNED,
> prot TINYINT UNSIGNED NOT NULL DEFAULT 0,
> tos TINYINT UNSIGNED NOT NULL DEFAULT 0,
> tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0,
> INDEX unix_secs (unix_secs),
> INDEX srcaddr (srcaddr),
> INDEX dstaddr (dstaddr),
> INDEX srcport (srcport),
> INDEX dstport (dstport)
> Now, as time progresses the queires are getting slower and slower.
> I know this is expected, so I am curious as to how I can have a main
> table that has all traffic, so that I can do monthly/yearly reports,
> and also have a daily table so that I can quickly do reports every
> minute or so on that data.
> I have read up a bit on merge tables (this is probably the answer) but
> I am unsure as to how you trigger the changes. ie, how do you do the
> rollover after every 24hours?
> Any thoughts, or a pointer in the right direction would be greatly
> Paul Halliday
> "Diplomacy is the art of saying "Nice doggie!" till you can find a rock."
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263