How to improve performance in "like '%variable%'"?

I have this query in MySQL:

select *
from alias where
name like '%jandro%';

Which results are:

Jandro, Alejandro

The index on name cannot be applied to higher performance because it is a range f开发者_运维百科ilter. Is there any way of improving that query performance?

I have tried with a full-text index, but it only works for complete words.

I also tried with a MEMORY ENGINE table, and it is faster, but I would like a better choice.

EDIT

I think i will have just to accept this for now:

select *
from alias where match(name) against ('jandro*' in boolean mode);

---

I've done this in the past (not on MySQL, and before full text searching was commonly available on database servers) by creating a lookup table, in which I created all left-chopping substrings to search on.

In my case, it was merited - a key user journey involved searching for names in much the way you suggest, and performance was key.

It worked with triggers on insert, update and delete.

Translated to your example:

Table alias

ID          name
1           Jandro
2           Alejandro

Table name_lookup

alias_id          name_substring
1                 Jandro
1                 andro
1                 ndro
1                 dro
1                 ro
2                 Alejandro
2                 lejandro
2                 ejandro
2                 jandro
2                 andro
2                 ndro
2                 dro
2                 ro

Your query then becomes

select alias_id, name
from alias a, 
     name_lookup nl
where a.id = ni.alias_id
and   ni.name_substring like 'andro%'

That way, you hit the index on the name_substring table.

It's only worth doing for common queries on huge data sets - but it works, and it's quick.