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]