> Hi, Hi, thank you for your reply.
> Since b can have only two values, making it the first column in the > index columns will not be efficient, Why? > but since b is many distinct values ...I suppose you mean 'a'... > ordering on this key first makes sense. Also you are always > querying with a in the where clause, it should be the first key. I'm always using a _and_ b. > The > second in the index order can be determined according to the data > in the table and the retrieval requirement. If your query always uses > a and b only then keep only these two in the compound index else No, I also use c and d. Most often a/b/c, sometimes a/b and sometimes a/b/c/d. Note that this is the primary key, all four values are needed to identify a unique row. > I would suggest keeping the b column in the third or fourth position. No, I can't do that, because I need to fetch rows based on a and b only. > Also the performance depends on the select statements you are > issuing. Yes, of course. :) We use rather complex dynamically created select statements, about 150 lines with about 20 LEFT JOIN's... I won't bother you with the details. ;o) I know that bot ways (a/b/c/d and /b/a/c/d) work, because if I issue a SELECT without providing the 'b', the response time is a minute or two, when I provide 'b', the response time is less than a second. This goes for both combinations of the primary key. Both are fast, what I wanted to know was wich of them is faster, because I want to speed up this app. as much as possible. Again, thank you for your time! :o) -- Roger --------------------------------------------------------------------- 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
