> > What is the exact difference between > this > index(col1,col2). > that > index(col1),index(col2). > and this > index(col1),index(col2),index(col1,col2). > > I read in the manual about it, but it's not really clear about the way > to should be used / created. > > Any sql pseudo-teacher in here? :) > > Thanks a lot, > > Etienne >
I am not sure I understand your question but the following may be helpful. Assume rows and columns with values as follows: Row col1 col2 1 4 5 2 2 5 3 2 3 4 4 4 Index (col1,col2), rows appear in this order ascending: 3 2 3 2 2 5 4 4 4 1 4 5 Index (col1) rows can appear in this order ascending: 2 2 5 3 2 3 1 4 5 4 4 4 or 3 2 3 2 2 5 1 4 5 4 4 4 or 2 2 5 3 2 3 4 4 4 1 4 5 etc.... col2 does not affect order if the index is on col1. The actual order cannot be determined but the rows are ordered properly by the index. Index (col2) rows can appear in this order ascending: 3 2 3 4 4 4 1 4 5 2 2 5 or 3 2 3 4 4 4 2 2 5 1 4 5 etc.... col1 does not affect order if the index is on col2. The actual order cannot be determined but the rows are ordered properly by the index. index(col1), index(col2), index(col1,col2) are independent of each other. Each index orders the rows according to the value in the column (or) columns that make up the index. In MySQL the indexes are stored as B-trees that are searched using a binary search algorithm. Also the statement "The actual order cannot be determined but the rows are ordered properly by the index." can be argued that with enough information about the rows and the indexes the order can be determined but that determination would not be controlled by the values in the column not indexed. The order of the creation of the rows and the implementation of the of the indexing code would be the determinate. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
