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?
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
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:
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.
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.
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
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.