I am trying to analyze a query that is taking forever.
I am new to this so or rather desperate.
I would assume that my query would be faster, if
the event id (*.cid) Primary key were used to search
for rows in the iphdr table with the same event id,
but I don't think that is happening. Could some one tell
me if my assumption is correct and how can I get the
query to use the primary *.cid keys. It appears that
the developer created a table that joined signature, iphdr, and event
tables together to solve the problem of speed to create the Acid_event
table. I would still like to know how can I improve
my query?
thank you,
Raymond
-----Original Message-----
From: Raymond Jacob
Sent: Monday, July 26, 2004 19:50
To: Jacob, Raymond A Jr
Subject: query analysis
version: MySQL-Max-3.23.58-1
DESC iphdr;
DESC event;
DESC signature;
DESC acid_event
EXPLAIN
select count(ip_dst) as ip_dst_count, inet_ntoa(ip_dst)
from iphdr, event , signature
where ( event.timestamp > now() - interval 24 hour ) and
(event.cid = iphdr.cid and event.signature = signature.sig_id
and signature.sig_sid = 1432 )
group by ip_dst order by ip_dst_count desc limit 100;
Iphdr
Field|Type|Null|Key|Default|Extra
sid|int(10) unsigned||PRI|0|
cid|int(10) unsigned||PRI|0|
ip_src|int(10) unsigned||MUL|0|
ip_dst|int(10) unsigned||MUL|0|
ip_ver|tinyint(3) unsigned|YES||NULL|
ip_hlen|tinyint(3) unsigned|YES||NULL|
ip_tos|tinyint(3) unsigned|YES||NULL|
ip_len|smallint(5) unsigned|YES||NULL|
ip_id|smallint(5) unsigned|YES||NULL|
ip_flags|tinyint(3) unsigned|YES||NULL|
ip_off|smallint(5) unsigned|YES||NULL|
ip_ttl|tinyint(3) unsigned|YES||NULL|
ip_proto|tinyint(3) unsigned|||0|
ip_csum|smallint(5) unsigned|YES||NULL|
event:
Field|Type|Null|Key|Default|Extra
sid|int(10) unsigned||PRI|0|
cid|int(10) unsigned||PRI|0|
signature|int(10) unsigned||MUL|0|
timestamp|datetime||MUL|0000-00-00 00:00:00|
Signature:
Field|Type|Null|Key|Default|Extra
sig_id|int(10) unsigned||PRI|NULL|auto_increment
sig_name|varchar(255)||MUL||
sig_class_id|int(10) unsigned||MUL|0|
sig_priority|int(10) unsigned|YES||NULL|
sig_rev|int(10) unsigned|YES||NULL|
sig_sid|int(10) unsigned|YES||NULL|
Acid_event:
Field Type Null Key Default Extra
sid int(10) unsigned PRI 0
cid int(10) unsigned PRI 0
signature int(10) unsigned MUL 0
sig_name varchar(255) YES MUL NULL
sig_class_id int(10) unsigned YES MUL NULL
sig_priority int(10) unsigned YES MUL NULL
timestamp datetime MUL 0000-00-00 00:00:00
ip_src int(10) unsigned YES MUL NULL
ip_dst int(10) unsigned YES MUL NULL
ip_proto int(11) YES MUL NULL
layer4_sport int(10) unsigned YES MUL NULL
layer4_dport int(10) unsigned YES MUL NULL
Query Analysis
table|type|possible_keys|key|key_len|ref|rows|Extra
event|range|sig,time|time|8|NULL|39382|where used; Using temporary; Using filesort
iphdr|ALL|NULL|NULL|NULL|NULL|375383|where used
signature|eq_ref|PRIMARY|PRIMARY|4|event.signature|1|where used
cartesian product= 14,783,333,306
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
