Well I have a column varchar for password on my table and at some scripts i make queries like:
length(column_varchar) < 10
Indexes are used to index content (field value), not the length of the field, therefore no index can help in the above query. (N. B. you could have a sparate field that has the content length and index that separate field.) Also, the password should be stored in a hashed format, so all password lengths should be the same, or at least should not be a criteria for selection.
No, you should not index all columns that will be used in a where criteria. Selecting the optimal index structure is a complicated and very broad topic. Always consider the following points when trying to determine what fields (or combination of fields) to index:
Indexes speed up selects, but slow down data modification, since you have to update the index as well, not just the column's value.
MySQL can use only 1 index per table in a query.
MySQL uses the selectivity of the indexes to determine which one to use. A field that can have 2 values only (yes / no, true / false) is not selective enough, so do not trouble yourself with indexing it.
Always use the
explain command to check which indexes your queries use.