Snort mailing list archives
RE: How to create the DB indices with postgresql
From: "Hutchinson, Andrew" <Andrew.Hutchinson () Vanderbilt edu>
Date: Thu, 27 Jun 2002 08:05:45 -0500
Daniel: Hopefully this will answer a couple of your questions. 1. Your syntax on the CREATE INDEX statement looks good. The name of the index should not matter - however, I usually use a notation that will let me know that that entity is an index (something like "idx_event_timestamp" or "ev_ts_idx" or something else that has meaning to you and will be decipherable for others). 2. Without the error messages you are receiving regarding UNIQUE keys, it's difficult to say what is going on. If you try to specify a unique key on a column that contains duplicate values, or try to insert a duplicate value into a unique column, postgresql will raise an error condition. In this specific instance (event.timestamp) I would NOT specify UNIQUE, because it is very possible that two events can have the same timestamp. 3. I don't use acid, but I would assume from the fields referenced by "acid_ag_alert.ag_sid + acid_ag_alert.ag_cid" that they are referring to the creation of a compound index. This would be accomplished like this: CREATE UNIQUE INDEX idx_ag_sid_cid ON acid_ag_alert(ag_sid,ag_cid); I speculate that UNIQUE is ok in this instance b/c the cid.sid combo should be unique to each event (can't have the safe event id from the same sensor twice!). One caveat is to always be certain to use the whole compound index if you write your own sql statements. Referencing a single key of a compound index gives you no benefit. Additionally, if you query and want to take advantage of a compound key, you MUST use the key elements IN ORDER (i.e. if your index uses ag_sid.ag_cid and your query references ag_cid first, the compound index will not be used.). At least this is the case in MySQL - I guess that postgresql could be different, but I don't think that it is. Hope that this helps, Andrew -----Original Message----- From: Daniel Lang [mailto:dl () leo org] Sent: Thursday, June 27, 2002 6:04 AM To: snort-users () lists sourceforge net Subject: [Snort-users] How to create the DB indices with postgresql Hi, sorry if this is FAQ (it was not answerered in the Snort/ACID FAQ), geocrawler doesn't seem to support searching the archives, so after some fruitless search, I dare to ask directly. "ACID FAQ B-9 PostgreSQL optimizations" suggest to add indexes to the databases, mentioning fields, that should have indexes created. Now I'm not an SQL expert, and I'm not sure how to create these indexes. The CREATE INDEX command needs a name for the created index, and I don't know, if the name needs to be a specific one. For the first field (event.timestamp) I tried: CREATE INDEX event_timestamp ON event (timestamp); and such alike for the other fields, but it seemed not to result in any benefit. Also I got error messages sometimes regarding a 'unique index' (I did not specify UNIQUE anywhere). Further some field descriptions from the FAQ are mysterious to me: (DB schema < v103) iphdr.ip_src0 + iphdr.ip_src1 + iphdr.ip_src2 + iphdr.ip_src3 This seems only to be required if the schema version is below 103? As far as I can tell, I'm using 105, so I omitted them just. Also I'm not sure, what is meant by: acid_ag_alert.ag_sid + acid_ag_alert.ag_cid The addition of an index or concatenation? How would one specify that suggestion. Please clarify if the names of the indexes are important, and which names to use, and how to create these '+' connected indexes. Thank's a lot. Best regards, Daniel -- IRCnet: Mr-Spock - Truth lies in the eye of the beholder - *Daniel Lang * dl () leo org * +49 89 289 25735 * http://www.leo.org/~dl/* ------------------------------------------------------- Sponsored by: ThinkGeek at http://www.ThinkGeek.com/ _______________________________________________ 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 ------------------------------------------------------- Sponsored by: ThinkGeek at http://www.ThinkGeek.com/ _______________________________________________ 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:
- How to create the DB indices with postgresql Daniel Lang (Jun 27)
- <Possible follow-ups>
- RE: How to create the DB indices with postgresql Hutchinson, Andrew (Jun 27)
- Re: How to create the DB indices with postgresql Daniel Lang (Jun 27)
- RE: How to create the DB indices with postgresql Hutchinson, Andrew (Jun 27)
- Re: How to create the DB indices with postgresql Ben (Jun 27)
- Re: How to create the DB indices with postgresql Daniel Lang (Jun 28)
- Re: How to create the DB indices with postgresql Ben (Jun 27)