Best type of indexing when there is LIKE clause [duplicate]

Here is my query:

SELECT name, usage_guidance, total_used_num
FROM tags
WHERE
 ( name LIKE CONCAT('%', ?, '%') OR
   usage_guidance LIKE CONCAT(?, '%') )
 AND name NOT IN ($in)
ORDER BY name LIKE CONCAT('%', ?, '%') DESC, name ASC
LIMIT 6

Which one is the best index?

• tags(name,usage_guidance)
• tags(usage_guidance,name)
• tags(name)
• tags(usage_guidance)

Or is there any better option?! You know, when LIKE comes in, I'm getting confused bout creating indexes. Because LIKE %something would never take any benefit of indexes. Also in query above I have both AND, OR and IN .. That's why I asked this question to know your opinion about it too.

Here is my table structure:

CREATE TABLE `tags` (
    `id` int(11) NOT NULL,
    `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `usage_guidance` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `parent_id` int(11) UNSIGNED DEFAULT NULL,
    `related` int(11) UNSIGNED DEFAULT NULL,
    `total_used_num` int(11) UNSIGNED NOT NULL,
    `date_time` int(11) UNSIGNED NOT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I'm trying to make a autocomplete suggestion query. Something like this:

Solution 1:

Yep, what you have here is a database killer

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

Source: http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

So that means your LIKE query cannot use the index and then you have two likes connected with an OR. If that's not enough, you have thrown in a NOT IN comparison as well.

But fortunately, the second LIKE expression isn't so bad, it doesn't start with a wildcard. So your best hope is to create a composite index on usage_guidance, name

If you could post your SHOW CREATE TABLE and a few lines of sample data + the expected output, we might get an idea if there is a way to rewrite this query.