On Wed, 2001-12-05 at 15:01, Robert Alexander wrote:
> At 14:45 -0800 2001/12/05, Florin Andrei wrote:
> >The problem is, MySQL-3.23.46 takes forever to return from SELECT (i let
> >it run over night, in the morning i still didn't got any results, so i
> >killed the query).
>
> the hardware and OS
SGI 1200 (dual PIII/800, 768MB RAM, SCSI)
Linux Red Hat 7.2
> the actual query (formatted to be readable, please :> )
SELECT event.cid, iphdr.ip_src, iphdr.ip_dst, tcphdr.tcp_dport FROM
event, iphdr, tcphdr WHERE event.cid = iphdr.cid AND event.cid =
tcphdr.cid AND tcphdr.tcp_flags = '2';
When i ran the query, it just sat there, doing nothing: no disk
activity, CPU usage was 0%... As i said, after several hours i had to
kill it.
This is actually a database for Snort; i'm sniffing a network and
recording relevant info about IP packets into the database.
The event table contains only the event id (cid) and the timestamp for
the IP packet.
The iphdr table contains the source- and destination- IP for the packet
(identified by cid).
The tcphdr table contains TCP-specific info for the packet (identified
by cid).
I need to extract the source- and destination- IP address for certain
packets (TCP with SYN flag set). And maybe ORDER BY iphdr.ip_src, but
that's not mandatory.
The actual table structure is this:
CREATE TABLE event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
PRIMARY KEY (sid,cid),
INDEX sig (signature),
INDEX time (timestamp));
CREATE TABLE iphdr ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
ip_src INT UNSIGNED NOT NULL,
ip_dst INT UNSIGNED NOT NULL,
ip_ver TINYINT UNSIGNED,
ip_hlen TINYINT UNSIGNED,
ip_tos TINYINT UNSIGNED,
ip_len SMALLINT UNSIGNED,
ip_id SMALLINT UNSIGNED,
ip_flags TINYINT UNSIGNED,
ip_off SMALLINT UNSIGNED,
ip_ttl TINYINT UNSIGNED,
ip_proto TINYINT UNSIGNED NOT NULL,
ip_csum SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX ip_src (ip_src),
INDEX ip_dst (ip_dst));
CREATE TABLE tcphdr( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
tcp_sport SMALLINT UNSIGNED NOT NULL,
tcp_dport SMALLINT UNSIGNED NOT NULL,
tcp_seq INT UNSIGNED,
tcp_ack INT UNSIGNED,
tcp_off TINYINT UNSIGNED,
tcp_res TINYINT UNSIGNED,
tcp_flags TINYINT UNSIGNED NOT NULL,
tcp_win SMALLINT UNSIGNED,
tcp_csum SMALLINT UNSIGNED,
tcp_urp SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX tcp_sport (tcp_sport),
INDEX tcp_dport (tcp_dport),
INDEX tcp_flags (tcp_flags));
(it's the typical Snort database, and i don't want to change it, because
many applications related to Snort expect it to be this way)
--
Florin Andrei
"Engineering does not require science." - Linus Torvalds
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
