Awsj2hd8 Awsj2hd8 - 1 year ago 35
PHP Question

Column varchar and issue about index or fulltext?

Well I have a column varchar for password on my table and at some scripts i make queries like:

length(column_varchar) < 10

My question is if i put a index on this column, it will help? or in this case should use fulltext? or don't need a index?

Another question i need to use index in all columns that will be used in 'where'?

Thanks in advanced.


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:

  1. Indexes speed up selects, but slow down data modification, since you have to update the index as well, not just the column's value.

  2. MySQL can use only 1 index per table in a query.

  3. 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.

  4. Always use the explain command to check which indexes your queries use.