Hi, you have to play with "explain" to see which index is used in your queries. Since you defined only mono-column indexes, i think they are not used in queries with multi-criteria search.
Consider adding indexes with all used columns and eventually drop the not used ones to not slow updates and inserts. merge (Myisam) tables can help you to partition the data on relevant keys used in the queries. But i'm not sure it's certainly good because you then loose the innodb row locking which is better in your situation. you can also consider archiving of old (and not used) data. Finally, you can prepare agregation tables every day (or hour) for example if you can consider a gap of data in the results. Mathias Selon Paul Halliday <[EMAIL PROTECTED]>: > 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/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
