There are many ways to use indexes. All of them are optimized for a few special cases. Therefore, it’s better to understand and benchmark them before applying.
- A column must be isolated in a query in order to be used with indexes.
- Advice: Simplify your query, so that a column always be in one side of the criteria
- We can reduce the size of indexes of string columns by indexing only prefix of the column. However, using only a part of the column can also decrease selectivity, which leads to less efficient index. Therefore, we must balance between size and selectivity.
- Only a few storage engines support this indexes (like InnoDB). Most of other storage engines (like MyISAM) don’t support it.