It is not quite possible - there are several hundred packets per second - hence 
several hundred fields with equal 
timestamps. In order to use primary key all fields have to be unique...

Peter

On Tue, Sep 30, 2003 at 03:39:08PM -0700, James Kelty wrote:
> Maybe making it the PRIMARY KEY will help?
> 
> -James
> 
> Peter Rabbitson wrote:
> >Hello all, 
> >I am trying to set up a complete trraffic accounting using Ulogd-mysql. 
> >Since the whole idea of the project is to be able to select rows based on 
> >the timestamp value indexing is a must. The index files get created with 
> >no problem at all. However explain select refuses to use the index (see 
> >below):
> >
> >   mysql> select version();
> >   +-----------+
> >   | version() |
> >   +-----------+
> >   | 4.0.14 |
> >   +-----------+
> >   1 row in set (0.00 sec)
> >
> >   mysql> describe ulog;
> >   +-------------+----------------------+------+-----+---------+-------+
> >   | Field | Type | Null | Key | Default | Extra |
> >   +-------------+----------------------+------+-----+---------+-------+
> >   | pkt_time | timestamp(12) | YES | MUL | NULL | |
> >   | oob_prefix | varchar(32) | YES | | NULL | |
> >   | oob_mark | int(10) unsigned | YES | | NULL | |
> >   | oob_in | varchar(32) | YES | | NULL | |
> >   | oob_out | varchar(32) | YES | | NULL | |
> >   | ip_saddr | int(10) unsigned | YES | | NULL | |
> >   | ip_daddr | int(10) unsigned | YES | | NULL | |
> >   | ip_protocol | tinyint(3) unsigned | YES | | NULL | |
> >   | ip_tos | tinyint(3) unsigned | YES | | NULL | |
> >   | ip_ttl | tinyint(3) unsigned | YES | | NULL | |
> >   | ip_totlen | smallint(5) unsigned | YES | | NULL | |
> >   | tcp_sport | smallint(5) unsigned | YES | | NULL | |
> >   | tcp_dport | smallint(5) unsigned | YES | | NULL | |
> >   | tcp_urg | tinyint(4) | YES | | NULL | |
> >   | tcp_ack | tinyint(4) | YES | | NULL | |
> >   | tcp_psh | tinyint(4) | YES | | NULL | |
> >   | tcp_rst | tinyint(4) | YES | | NULL | |
> >   | tcp_syn | tinyint(4) | YES | | NULL | |
> >   | tcp_fin | tinyint(4) | YES | | NULL | |
> >   | udp_sport | smallint(5) unsigned | YES | | NULL | |
> >   | udp_dport | smallint(5) unsigned | YES | | NULL | |
> >   | icmp_type | tinyint(3) unsigned | YES | | NULL | |
> >   | icmp_code | tinyint(3) unsigned | YES | | NULL | |
> >   | icmp_echoid | smallint(5) unsigned | YES | | NULL | |
> >   +-------------+----------------------+------+-----+---------+-------+
> >   24 rows in set (0.00 sec)
> >
> >   mysql> show index from ulog;
> >+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------
> >   -----+---------+
> >   | Table | Non_unique | Key_name | Seq_in_index | Column_name | 
> >   Collation | Cardinality | Sub_part | Packed | Null |
> >   Index_type | Comment |
> >+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------
> >   -----+---------+
> >   | ulog | 1 | tim | 1 | pkt_time | A | 45070 | NULL | NULL | | BTREE | |
> >+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------
> >   -----+---------+
> >   1 row in set (0.00 sec)
> >
> >Data is being stored inside no problem at all:
> >
> >   mysql> select * from ulog limit 3000,1;
> >+--------------+------------+----------+--------+---------+------------+-----------+-------------+--------+--------+------
> >-----+-----------+-----------+---------+---------+---------+---------+---------+---------+-----------+-----------+--------
> >   ---+-----------+-------------+
> >   | pkt_time | oob_prefix | oob_mark | oob_in | oob_out | ip_saddr | 
> >   ip_daddr | ip_protocol | ip_tos | ip_ttl | ip_totlen |
> >   tcp_sport | tcp_dport | tcp_urg | tcp_ack | tcp_psh | tcp_rst | tcp_syn 
> >   | tcp_fin | udp_sport | udp_dport | icmp_type |
> >   icmp_code | icmp_echoid |
> >+--------------+------------+----------+--------+---------+------------+-----------+-------------+--------+--------+------
> >-----+-----------+-----------+---------+---------+---------+---------+---------+---------+-----------+-----------+--------
> >   ---+-----------+-------------+
> >   | 030925023218 | rawin | 0 | eth0 | | 3645603842 | 217539170 | 6 | 0 | 
> >   44 | 80 | 7777 | 32784 | 0 | 1 | 1 | 0 | 0 | 0 |
> >   NULL | NULL | NULL | NULL | NULL |
> >+--------------+------------+----------+--------+---------+------------+-----------+-------------+--------+--------+------
> >-----+-----------+-----------+---------+---------+---------+---------+---------+---------+-----------+-----------+--------
> >   ---+-----------+-------------+
> >   1 row in set (0.02 sec)
> >
> >However for some reason MySQL under any circumstances does not want to 
> >take advantage of the index when doing time-sensitive selects, which takes 
> >forever to perform without an index:
> >
> >   mysql> explain select * from ulog where pkt_time=030925023218;
> >   +-------+------+---------------+------+---------+------+--------+-------------+
> >   | table | type | possible_keys | key | key_len | ref | rows | Extra |
> >   +-------+------+---------------+------+---------+------+--------+-------------+
> >   | ulog | ALL | tim | NULL | NULL | NULL | 991541 | Using where |
> >   +-------+------+---------------+------+---------+------+--------+-------------+
> >   1 row in set (0.00 sec)
> >
> >If I index ANY other filed the indexing works like a charm, but the whole 
> >idea of the excercise is to take advantage of the packet logging time. 
> >Anyone with fresh ideas is welcome.
> >
> >Peter
> >
> >
> 
> 
> -- 
> James Kelty
> E-Commerce / Financial Systems Administrator
> Portland State University
> 503.725.9152
> [EMAIL PROTECTED]
> 
> MySQL 3.23.58: up 10 days, processed 22,867,360 queries (24/sec. avg)



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to