Search code examples
performancesearchmezzanine

Slow Search in Mezzanine CMS


Slow Search in Mezzanine CMS. I use Mezzanine CMS. I find it too slow when searching for something. The size of blog_blogpost is about 60,000 records.

The example site is www.chinalaw.vip .

Please help!

enter image description here

enter image description here


Solution

  • It was caused by a case-insensitive match (icontains) and without any index. I fix the problems with the steps:

    1、Change the case-insensitive match to the case-sensitive match in mezzanine.

        excluded = [reduce(iand, [~Q(**{"%s__contains" % f: t[1:]}) for f in
            self._search_fields.keys()]) for t in terms if t[0:1] == "-"]
        required = [reduce(ior, [Q(**{"%s__contains" % f: t[1:]}) for f in
            self._search_fields.keys()]) for t in terms if t[0:1] == "+"]
        optional = [reduce(ior, [Q(**{"%s__contains" % f: t}) for f in
            self._search_fields.keys()]) for t in terms if t[0:1] not in "+-"]
    

    2、Change the case-insensitive match to the case-sensitive match in path django\contrib\admin\options.py in django.

    def get_search_results(self, request, queryset, search_term):
        """
        Return a tuple containing a queryset to implement the search
        and a boolean indicating if the results may contain duplicates.
        """
        # Apply keyword searches.
        def construct_search(field_name):
            if field_name.startswith('^'):
                return "%s__istartswith" % field_name[1:]
            elif field_name.startswith('='):
                return "%s__iexact" % field_name[1:]
            elif field_name.startswith('@'):
                return "%s__search" % field_name[1:]
            # Use field_name if it includes a lookup.
            opts = queryset.model._meta
            lookup_fields = field_name.split(LOOKUP_SEP)
            # Go through the fields, following all relations.
            prev_field = None
            for path_part in lookup_fields:
                if path_part == 'pk':
                    path_part = opts.pk.name
                try:
                    field = opts.get_field(path_part)
                except FieldDoesNotExist:
                    # Use valid query lookups.
                    if prev_field and prev_field.get_lookup(path_part):
                        return field_name
                else:
                    prev_field = field
                    if hasattr(field, 'get_path_info'):
                        # Update opts to follow the relation.
                        opts = field.get_path_info()[-1].to_opts
            # Otherwise, use the field with icontains.
            return "%s__contains" % field_name
    
        use_distinct = False
        search_fields = self.get_search_fields(request)
        if search_fields and search_term:
            orm_lookups = [construct_search(str(search_field))
                           for search_field in search_fields]
            for bit in search_term.split():
                or_queries = [models.Q(**{orm_lookup: bit})
                              for orm_lookup in orm_lookups]
                queryset = queryset.filter(reduce(operator.or_, or_queries))
            use_distinct |= any(lookup_needs_distinct(self.opts, search_spec) for search_spec in orm_lookups)
    
        return queryset, use_distinct
    

    3、Create an index.

       CREATE EXTENSION pg_trgm;
    
       CREATE INDEX blog_blogpost_content_index ON blog_blogpost USING gin (title gin_trgm_ops,description gin_trgm_ops,content gin_trgm_ops,keywords_string  gin_trgm_ops);