search - Can I create a MySQL index for LIKE searches with both left and right wildcards? -

i’m using mysql 5.5.37. have table column

`name` varchar(100) collate utf8_bin not null 

and intend have partial searches on name column like

select * organnization name ‘%abc%’ 

note want search string “abc” occur anywhere, not @ beginning. given this, there index can use on column optimize query execution?

if expect few matching results only, can still create index on name column speed queries, of primary key.

if table have primary key like

org_id int not null auto_increment primary key, name varchar(100) collate utf8_bin not null, desc varchar(200) collate utf8_bin not null, size int, ....

you can create index on (name, org_id)

and query this:

select * orgnizations o1 join (select org_id orgnizations name '%abcd%' ) o2 using (org_id)  

should faster original query

if need 1 or 2 other columns name searching, can include columns in name index , queries like

select org_id, name, size orgnizations name '%abcd%' 

will still faster full table scan