Do you mean that your table has a total of 10000 rows? If yes, there is (generally) no need to add column b and/or c to the primary key, as this makes the information redundant (already A is unique).
If you mean that you table has 40000 rows (so 10K A x 2 B x 2 C) and only the combination of AxBxC makes the record unique, you have to use all 3 for the primary key. As for optimization, in theorie the order should NOT make any difference. If you first search 10K entries (A) and THEN search 2 entries (B) and THEN search 2 entries (C) gives you the same lookup hits than first lookup 2 entries (C) THEN lookup 2 entries (B) and THEN lookup 10K entries (A). But however exactly this is implemented is beyond my knowledge. As for general indexing and tuning, it is always better to use keys with high cardinality than keys with low one. Cheers /rudy -----Original Message----- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: donderdag 10 juli 2003 3:05 To: [EMAIL PROTECTED] Subject: Why is it better to have a composite key with the largest cardinality 1st? Say I have column A with 10000 distinct values column B and C with 2 distinct values and for simplicity sake the column type is an int. If I wanted a composite key why should I make the order of the key A,B,C I read someplace that faster lookups happen with the leftmost index, being an index with a large cardinality. Is this true? Is this a hook for the optimizer? What is the internal logic for mySQL that makes this so? Is this a myth and have I been misled? -- Dathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
