Given > `SELECT * FROM logs WHERE login like '%andy%'` something you may not know is `LIKE` is case-insensitive by default. So when you index `login`, without David's suggestion to `collate nocase` that index cannot be used. An alternative is to force `LIKE` to be case-sensitive (there's a `PRAGMA` for that), or use `GLOB` (see below). Also as David mentioned, indexes can be used if what you search for is equal or a **prefix** of what is indexed. So use `login = 'andy'` or `login GLOB 'andy*'` (`GLOB` and `LIKE` are similar, just using different syntax, and [GLOB is case-sensitive](https://www.sqlite.org/lang_expr.html#glob)) can use the index, while `login LIKE 'andy%'` or `login GLOB '%andy%'` cannot. PS: Don't use double-quotes for string literal, use single-quotes instead.