Best type of indexing when there is LIKE clause

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:

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating