Index for where like

[Azu]

Hello

I have a search on my site and basically it uses this query

 

select `id`,`name` from `database`.`data` where `name` like '%test%' limit 20,40

 

And I have the id AND the name column both indexed, so that it should be using the index to go fast.

 

The problem is, it's ignoring the index!

 

explain select `id`,`name` from `database`.`data` where `name` like '%test%' limit 20,40

 

returns

 

select_type=simple

table=data

type=all

possible_keys=(null)

key=(null)

key_len=(null)

ref=(null)

rows=22515

extra=using where

 

And it takes around 100ms per query.

 

Please tell me how I can fix it so that it will use the index.

 

ID is unique and has a unique index.

And name has an index (not unique).

[Wildbug]

It's because you're using a wildcard at the beginning of the string, LIKE '%test%'.  The index indexes based on the beginning of the string.