In MySQL 8, is there an index I can create to speed up LIKE searches?

Question

I'm using Django 3, Python 3.8 and MySql 8. I have the following Django model in which I create a search based on a partial name ...

class Coop(models.Model):
    objects = CoopManager()
    name = models.CharField(max_length=250, null=False)
    types = models.ManyToManyField(CoopType, blank=False)
    addresses = models.ManyToManyField(Address)
    enabled = models.BooleanField(default=True, null=False)
    phone = models.ForeignKey(ContactMethod, on_delete=models.CASCADE, null=True, related_name='contact_phone')
    email = models.ForeignKey(ContactMethod, on_delete=models.CASCADE, null=True, related_name='contact_email')
    web_site = models.TextField()

...

# Look up coops by a partial name (case insensitive)
def find_by_name(self, partial_name):
    queryset = Coop.objects.filter(name__icontains=partial_name, enabled=True)
    print(queryset.query)
    return queryset

The code above produces this query ...

SELECT `directory_coop`.`id`, `directory_coop`.`name`, `directory_coop`.`enabled`, `directory_coop`.`phone_id`, `directory_coop`.`email_id`, `directory_coop`.`web_site` FROM `directory_coop` WHERE (`directory_coop`.`enabled` = True AND `directory_coop`.`name` LIKE %Credit%)

Below is the table that Django migrations produced. Is there any kind of index or other adjustment I can make to speed up these queries -- specifically, the "name LIKE %Credit%" part?

CREATE TABLE `directory_coop` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  `phone_id` int DEFAULT NULL,
  `email_id` int DEFAULT NULL,
  `web_site` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `directory_coop_email_id_c20abcd2` (`email_id`),
  KEY `directory_coop_phone_id_4c7e2178` (`phone_id`),
  CONSTRAINT `directory_coop_email_id_c20abcd2_fk_directory_contactmethod_id` FOREIGN KEY (`email_id`) REFERENCES `directory_contactmethod` (`id`),
  CONSTRAINT `directory_coop_phone_id_4c7e2178_fk_directory_contactmethod_id` FOREIGN KEY (`phone_id`) REFERENCES `directory_contactmethod` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=993 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Does this answer your question? [How to speed up SELECT .. LIKE queries in MySQL on multiple columns?](https://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns) — JPG, Aug 14 '20 at 13:31
you can set a primary key in your models by adding `pk=True` in a specific field. you can also set `db_index`(read about those here https://docs.djangoproject.com/en/3.1/ref/models/fields/#db-index) but in general I would suggest to skip the ORM and write Raw queryies if you know what you are doing and the ORM does not fullfill your needs... — hansTheFranz, Aug 14 '20 at 13:32
@ArakkalAbu, the accepted answer says "Full text search available for InnoDB tables with MySQL 5.6+." but it doesn't give a whole lot of other details. Is that what you were referring to? — Dave, Aug 14 '20 at 14:11
In that, I would like to point two things, 1). new MySQL versions have [full-text search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) support, which can be used in your case with a [*custom database function*](https://docs.djangoproject.com/en/dev/ref/models/expressions/#func-expressions). 2.) An index wouldn't speed up the ***`LIKE`*** query, which addresses the exact question you've asked. — JPG, Aug 14 '20 at 14:42
So are you saying that even if I changed my column type to "FULLTEXTSEARCH", that would not speed up the LIKE query? — Dave, Aug 14 '20 at 15:49
my non-expert understanding of full text searches is that source documents are usually broken down into terms on word boundaries which then get indexed. While LIKE (I believe) does an index seek on value before the wildcard. so probably one improvement you can make is to push wildcards as much to the right as possible. Or maybe build another index on reversed string i suppose it might get picked up if optimiser feels like it's less page reads — timur, Aug 16 '20 at 23:51

JPG · Answer 1 · 2020-08-17T03:37:19.540

0

The SQL query will not speedup with regular indexing with like operator, but you can use MySQL's full-text search functions on a FULLTEXT indexed column

For that, you need to index the name column manually using SQL query since Django doesn't have that functionality yet.

After enabling FULLTEXT index on the name column, you can use either the search lookup or Django Func(...) expression to query the data.

References

edited Aug 17 '20 at 03:37

answered Aug 17 '20 at 03:24

JPG

82,442
19
127
206

The Django Func link you referenced lists 'field_lower=Lower('field')', but that would only match on an exact string, right? It wouldn't match partials as the LIKE '%str%' used to do? – Dave Aug 18 '20 at 00:55
@Dave The [**`Func(...)`**](https://docs.djangoproject.com/en/dev/ref/models/expressions/#django.db.models.Func) is used to call *any valid Database functions*, which does not come with Django. I don't understand what do you mean by *"partial"* here? – JPG Aug 18 '20 at 02:45

In MySQL 8, is there an index I can create to speed up LIKE searches?

1 Answers1

References