Re: optimize MYSQL + ACID

[Erick Mechler <emechler () techometer net>]

:: 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