How can one optimize %XYZ% queries?

It is possible to improve %XYZ% (wildcard search) queries by forcing the optimizer
to scan all the entries from the index instead of the table.
This can be done by specifying hints.

If the index is physically smaller than the table (which is usually the case)
it will take less time to scan the entire index than to scan the entire
table.