Snort mailing list archives
Re: optimize MYSQL + ACID
From: Erick Mechler <emechler () techometer net>
Date: Fri, 21 Feb 2003 14:56:39 -0800
:: If you create a compound index in MySQL (like acid_ag_alert.ag_sid + :: acid_ag_alert.ag_cid), be certain that you query IN THAT ORDER if you :: wish to take advantage of the index. The MySQL optimizer will not :: recognize that you intend to use a compound index if you reverse the :: order, and you'll be sequential scanning until the cows come home. This isn't true. The MySQL optimizer is smart enough to order your SQL statment in such a way to take advantage of indexes if they exist. What is important, however, is that when you have multi column indexes you use the leftmost prefix of the index somewhere in your WHERE clause. For example, if you have a three column index on {col1,col2,col3} (in that order) a query like SELECT * FROM table WHERE col1 = 'something'; will take advantage of the index. A query like SELECT * FROM table WHERE col2 = 'something'; will not. There is no difference between the following two queries: SELECT * FROM table WHERE col1 = 'something' AND col2= 'somethingelse'; SELECT * FROM table WHERE col2 = 'something' AND col1= 'somethingelse'; The MySQL handbook has some more information on this which will probably explain it better than can I :) http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#MySQL_indexes Cheers - Erick ------------------------------------------------------- This SF.net email is sponsored by: SlickEdit Inc. Develop an edge. The most comprehensive and flexible code editor you can use. Code faster. C/C++, C#, Java, HTML, XML, many more. FREE 30-Day Trial. www.slickedit.com/sourceforge _______________________________________________ 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:
- optimize MYSQL + ACID Pete Davis (Feb 21)
- Re: optimize MYSQL + ACID Erick Mechler (Feb 21)
- <Possible follow-ups>
- RE: optimize MYSQL + ACID Hutchinson, Andrew (Feb 21)
- RE: optimize MYSQL + ACID Hutchinson, Andrew (Feb 21)
- Re: optimize MYSQL + ACID Erick Mechler (Feb 21)