hi,
how many index can be created for a mysql table if the table has 100 fields?

__________________
With,
J. Jeyaseelan
Everything Possible

Index can be created for all the fields.If its needed then we can create 100 indexes in the same table.

-V.Vadivelan

Hi,

Creating an index requires creating a key, and there's a limit placed on the number of keys allowed for a table. So the limit on indexes is governed by the number of keys you are allowed to create.

I have tested this in mysql 5.0.22-max-log.
-- Creating a table with 80 Columns.
-- while we creating 70 indexes for all columns in that table, indexes
created only for 64 columns and reported the below given error..

ERROR 1069: Too many keys specified. Max XX keys allowed

From the MySQL Documentaion Error: 1069 SQLSTATE: 42000 (ER_TOO_MANY_KEYS)
Message: Too many keys specified; max %d keys allowed.

It seems like that we can have maximun 64 indexs in a table. (Here we did't consider full text indexes)

I have reviewed some forums and it appears that this limit is controlled by the MAX_KEY variable in the source code (sql/unireg.h). Changing the value requires a recompile of MySQL.

- Priyan..

Lot of thanks,how many cluster & non cluster index possible in a table

Hi,


We can have only one Clustered Index in a Table as it will be unique for every table.

There can be any number of Non-Clustered Index in a table as its just a pointer to the data on a table.

__________________
-Murali..