1

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:

enter image description here

stack
  • 10,280
  • 19
  • 65
  • 117
  • @barmar, Using *full-text* isn't proper for what I'm looking for .. Because *full-text* doesn't support search-based-on-one-letter ..! – stack Dec 23 '16 at 16:55
  • If you can't use FTS, then you're just out of luck. There's no way to index searches that start with `%`. – Barmar Dec 23 '16 at 16:57
  • Maybe that's why SO doesn't use MySQL. – Barmar Dec 23 '16 at 16:58
  • @Barmar ..! Oh really?! SO doesn't use MySQL?! Shit ...! I thought it does. Actually I'm trying to make a website similar to SO. Well what DB is it using? – stack Dec 23 '16 at 16:59
  • SQL-Server: http://meta.stackexchange.com/questions/17988/what-database-does-stackoverflow-use – Barmar Dec 23 '16 at 17:00
  • Well @Barmar is right. FYI, postgresql can use indexes on these queries. – e4c5 Dec 23 '16 at 17:00

1 Answers1

3

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.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Thank you .. upvote .. Also please take a look at the update version of my question and tell me can I write that query better? – stack Dec 23 '16 at 16:53
  • Judging by your update, your query probably will not benefit from a full text search which the duplicate points to. Edit the question to reflect that and I will cast a reopen vote. – e4c5 Dec 23 '16 at 16:56