List:General Discussion« Previous MessageNext Message »
From:James Nobis Date:May 10 2005 7:07pm
Subject:Re: Solution to slow queries
View as plain text  
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

Thread
Solution to slow queriesPaul Halliday10 May
  • Re: Solution to slow queriesFrank Bax10 May
    • Re: Solution to slow queriesJohn McCaskey10 May
  • Re: Solution to slow queriesJames Nobis10 May
  • Re: Solution to slow queriesmfatene10 May
  • Re: Solution to slow queriesRoger Baklund10 May
    • Re: Solution to slow queriesPaul Halliday10 May
      • Re: Solution to slow queriesRoger Baklund11 May
RE: Solution to slow queriesTheRefUmp10 May
  • Re: Solution to slow queriesEric Jensen10 May