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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
