G M <[EMAIL PROTECTED]> wrote on 03/29/2005 01:54:17 AM: > hi all, > > in a table of say 100 fields, how many (maximum )numbers of indexes > can be created... > > thx in adv. >
This is really just a math problem as the database limits are much smaller than the number of possible index combinations. Given 100 items, how many different arrangements of those 100 items can exist if taken 1 at a time + 2 at a time + 3 at a time + 4 at a time + 5 at a time + ... + 100 at a time? 1 at a time = 100 different indexes 2 at a time = 100 * 99 = 9900 different indexes 3 at a time = 100 * 99 * 98 = 970200 4 at a time = 100 * 99 * 98 * 97 = 94109400 + ... + 100 at a time = 100 * 99 * 98 * 97 * ... * 2 * 1 = 100! = 9.3326215443944152681699238856267e+157 The actual limits on how many indexes and how many columns can be in each index are different for each storage engine: http://dev.mysql.com/doc/mysql/en/storage-engines.html http://dev.mysql.com/doc/mysql/en/innodb.html MyISAM: 64 indexes per table 16 columns per index 1000 bytes max per key (unless you recompile). MEMORY: 32 indexes per table 16 columns per index 500 bytes max per key etc. If I may be curious, what is your real design issue and why are you considering so many keys? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
