Hi, A compound index creates an index in the order specified. If key is (a,b,c,d) then the index is in the order a,b,cd. That is the index file keeps these columns ordered with a pointer to the actual row in the data table.
Since b can have only two values, making it the first column in the index columns will not be efficient, but since b is many distinct values ordering on this key first makes sense. Also you are always querying with a in the where clause, it should be the first key. 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 I would suggest keeping the b column in the third or fourth position. Also the performance depends on the select statements you are issuing. The point to note is that in the index file the rows are kept in the order of the index keys. If a,c,d,b is the order of the keys and you want to search for some values of c mysql cannot use the index, but if you search of some value of a and then some value for c, it can use the index. A bit of reading on how the index works will greately enhance the quality of query one writes. regards Anvar. At 05:18 PM 21/01/2002 +0100, you wrote: >Hi, > >I have some compound primary keys, all integer fields (a,b,c,d). In one >table we have by mistake made the key (b,a,c,d). We allways use at least >field a and field b in all lookups, and it seems to be working, but I wanted >to ask: does it matter? Should we change the 'wrong' primary key to >(a,b,c,d)? (Will mysql perform better/faster?) > >Also, I wonder what is the best order for my primary keys: > >a int This is the 'main' id, very many distinct values >b tinyint This is a source code, possible values are 1 or 2 >c smallint Some counter >d tinyint Another counter > > or > >b tinyint This is a source code, possible values are 1 or 2 >a int This is the 'main' id, very many distinct values >c smallint Some counter >d tinyint Another counter > >Which will perform faster, when all lookups use both a and b? > >-- >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 --------------------------------------------------------------------- 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
