Help with index on database

Is it a good idea to create an index on a field which is VARCHAR (500) ? I am going to do a lot of search in it, but I am not sure if creating an index on such a 'big开发者_如何学C' field is a good idea?

What do you think?

It is usually not a good idea since the index files will be huge and the search relatively slow. It is better to use a prefix of the field such as the first 32 or 64 characters of the field as an index. Another possibility is that if it makes sense use a full text index,.

In general it's a good idea to create indexes on fields that you'll use for search. But, depending on the use, there are better options:

• Full text search (from wikipedia): In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user.
• Partial index: (again, from wikipedia): In databases, a partial index, also known as filtered index is an index which has some condition applied to it so that it includes a subset of rows in the table.

Maybe you should consider giving more information on the use that index will have.

You should put indexes where often used queries will run faster, however, there are a number of issues to contemplate

• Indexes have a very limited size, eg. mssql has a 900 byte limit
• Many index may incur an overhead while writing (although minimal last time i benchmarked inserting a million entries on a table with 9 indexes
• Many indexes takes up precious space in the db
• many indexes may create deadlocks when inserting data

Also take a look at the documentation for the database you use. Most databases has support for text columns with efficient searching in them