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]
