indexing varchar fields - advice needed

[Anon]

I have a large table, containing at the moment 130k records. Now querires on this can take awhile specially when refering to the Name coloum. I have read that if you index only the first few characters of it then it will improve the performance markably. Is this corect? can any advice me on the dos and donts of indexing a char field.

[iifs044]

I'm not familiar enough with mySQL to know whether is supports partial field indexes like you suggset or not, however ....

The usefulness of an index depends on the nature of the query you're submitting. If you are submitting queries with WHERE clauses with either exact matches on the NAME column, or are using LIKE with a parameter that only has a wildcard at the end of the string (ex: LIKE 'Mitch%'), then indexes can be beneficial. If, however, you're using LIKE '%something here%', then an index is of no value (for that query).

Don't forget, however, that creating indexes does have drawbacks. For example, the size of the database will greatly increase with indexes. I have some tables which have more space allocated to indexes than to the actual data in the tables. The other thing is that INSERTS and UPDATES will be slower with an index (when the indexed columns are affected) because the indexes have to be updated, in addition to the table itself.

-- Mitch