For the last 2 days I was trying to optimize my SELECT query with LIKE operator. Even I tried to use FTS tables. It was fast, but it did not return the result as I intended. So I tried another way
I have this table:
Then created INDEX on key column:
Then I was getting data using this query:
ORDER BY KEY
Result was coming in 510ms. I tried to optimize my select query. Actually, this was not because of SELECT query. It was because of INDEX(index_key). I found that LIKE is not case sensitive so logical step is to make INDEX not case sensitive. It was done in this way:
Now same query as stated before, is returning result in 83ms which means more than 6 times faster. In applications, like a dictionary which required incremental search which difference is enormous.