List:General Discussion« Previous MessageNext Message »
From:TheRefUmp Date:May 10 2005 7:13pm
Subject:RE: Solution to slow queries
View as plain text  
I'm somewhat a newbee on this database but some observations:

As your table grows (and indexes) INSERTS will definitly slow because of the indexes.

Consider MySQL's version of Oracle's partitioning and using MERGE TABLES "feature". Just
remember that if you change 1 table, all of them have to be rebuilt the same way. 

Consider compressing the tables (Making them READ ONLY) with the MyISAM engine. The
updating table (current one) would not be available since it could not be compressed. 

 


Paul Halliday <paul.halliday@stripped> wrote:

>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
>
>

__________________________________________________________________
Switch to Netscape Internet Service.
As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register

Netscape. Just the Net You Need.

New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
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