mysql - Why is an index not used on a LIKE query with wildcards? -

this question has answer here:

• optimization of mysql search using “like” , wildcards 4 answers
• how speed select .. queries in mysql on multiple columns? 5 answers

although title column added index using following query:

alter table recipe add index title_idx (title) 

mysql doesn't use index query:

select * recipe title '%cake%'; 

i used explain keyword , key field null.

how solve it? have improve query.

you need full-text index match partially. consider normal index phone book: it's great finding people last name first name, "smith, john", useless finding people "ith" in name, you'll have go through entry entry manually matching.

any query says like '%x%' automatically table scan. not scale non-trivial sized tables.