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>:
> Hello,
>
> 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
> minutes.
>
> 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
> appreciated.
>
>
> Thanks.
>
> --
> _________________
> Paul Halliday
> http://dp.penix.org
>
> "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:
> http://lists.mysql.com/mysql?unsub=1
>
>
>
James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com
