Snort mailing list archives
RE: Speeding up mysql
From: "Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>
Date: Fri, 19 Oct 2001 13:03:11 -0500
I noted the same issue on the mysql database - it can be solved by adding an index as described, OR by rewriting the queries so that they reference the compound index rather than adding an additional index. I normally try to keep indicies to a minimum, because it significantly affects the speed of database writes. Our environment has a high volume of alerts from our sensors, and as such the writing speed is critical. To solve in indexing issue, just be sure to always use the compound index (inthe correct order) when writing queries. For example: "SELECT event.sid,event.cid,event.timestamp,iphdr.ip_src,iphdr.ip_dst FROM event,iphdr,signature WHERE signature.sig_id=$sigid AND signature.sig_id=event.signature AND event.sid=iphdr.sid AND event.cid=iphdr.cid AND event.timestamp > DATE_SUB(NOW(), interval $days day) ORDER BY event.timestamp DESC;"); If you always join on BOTH sid and cid, you can avoid the performance hit of the additional index altogether while speeding up queries by several orders of magnitude. Hope this helps, Andrew
-----Original Message----- From: snort-users-admin () lists sourceforge net@VANDERBILT On Behalf Of quentyn () fotango com Sent: Friday, October 19, 2001 12:24 PM To: Kevin Brown; snort-users () lists sourceforge net Subject: Re: [Snort-users] Speeding up mysqlKevin Brown wrote: What schema are you using? What version of Mysql? I'm using schema 104, Mysql 3.23.43 and according to the mysql manual (looking at mysql.com) it says that indexes are automatically made on Primary keynames. So doing a "show index from <tbl_name>" shows that eachofthe tables that contain a CID column are indexed already. Samethingwith signature in the event table and sig_id in the signature table.-----Original Message----- From: quentyn () fotango com [mailto:quentyn () fotango com] Sent: Friday, October 19, 2001 09:42 To: snort-users () lists sourceforge net Subject: [Snort-users] Speeding up mysqlit is the way mysql indexing works these are answers from 2 different people the primary key covers 2 fields, however the join refers to the 2ndary field and in the current indexing structure of MYSQL it would appear that this prevents the index being used, hence the queries performing a table scan (which takes ages). By adding a non unique index on the 2nd field you see a performance increase in multi column indexes mysql doesn't no use the index if you haven't referenced the 1st column of the index. or something or other..... Q -- ##################### Quentyn Taylor Sysadmin - Fotango ##################### "The way NT mounts filesystems is something I'd expect to find in a barnyard or on a stock-breeding farm." Mike Andrews, ASR _______________________________________________ Snort-users mailing list Snort-users () lists sourceforge net Go to this URL to change user options or unsubscribe: https://lists.sourceforge.net/lists/listinfo/snort-users Snort-users list archive: http://www.geocrawler.com/redir-sf.php3?list=snort-users
_______________________________________________ Snort-users mailing list Snort-users () lists sourceforge net Go to this URL to change user options or unsubscribe: https://lists.sourceforge.net/lists/listinfo/snort-users Snort-users list archive: http://www.geocrawler.com/redir-sf.php3?list=snort-users
Current thread:
- Speeding up mysql quentyn (Oct 19)
- <Possible follow-ups>
- RE: Speeding up mysql Kevin Brown (Oct 19)
- Re: Speeding up mysql quentyn (Oct 19)
- RE: Speeding up mysql Hutchinson, Andrew (Oct 19)