I’m using search api with database backend on a Drupal 9.3.3 site. I’ve indexed 26,000 nodes of a custom content type and these include a custom date field: field_display_date

The index is set up to do rendered html. Since I need users to be able to sort on field_display_date (descending) as well as relevance, I’ve exposed two sorts: relevance and date (using this custom date field) and have added field_display_date to the index in "date" format.

The ultimate goal is to be able get highly relevant search results but be able to sort them to see the most recent highly relevant results. Maybe there’s a completely different way to do this.

Problem: with the view all set up, key in a search term an execute. With the sort set to the default ‘relevance,’ returns are pretty quick. If I change the sort to ‘date’ (field_display_date: descending) and submit again, there is a very long wait time, often a gateway timeout.

On one that didn’t time out, the view sql and performance redout said this…

Query    Index: main2 Keys: 'test' Parsed keys: array (     '#conjunction' => 'AND',     0 => 'test',   ) Searched fields: rendered_item, title Sorting: field_display_date DESC Options: array (     'search_api_view' => 'object (Drupal\views\ViewExecutable)',     'search_api_base_path' => 'search2',   ) Title   Search2 Path    /search2 Query build time    1.43 ms Query execute time  2.81 ms View render time    43237.89 ms 

Why is the render time so high and any ideas on how to correct this? Also, is switching to apache solr likely to work better or have the same result? (Since setting up solr looks pretty involved and is going to take me quite a while, would like to know if it’s likely to be worth doing.)

By contrast, here’s the same view and index and search keyword sorted by relevance…

Query    Index: main2 Keys: 'test' Parsed keys: array (     '#conjunction' => 'AND',     0 => 'test',   ) Searched fields: rendered_item, title Sorting: search_api_relevance DESC Options: array (     'search_api_view' => 'object (Drupal\views\ViewExecutable)',     'search_api_base_path' => 'search2',   ) Title   Search2 Path    /search2 Query build time    1.23 ms Query execute time  2.68 ms View render time    2990.91 ms 

This seems to suggest that the search itself and getting the result are both pretty quick but for some reason loading the page is really slow?

Interestingly, bypassing search api entirely and just plopping the body field into a regular view and searching on the keyword gets a faster result, which makes me think I’m doing something incorrectly at a fundamental level.

Plain views filter with no index, sorted by field_display_date descending, searching the body field for the same keyword…

Title   Content Path    /admin/content/node2 Query build time    3.23 ms Query execute time  1.2 ms View render time    6291.28 ms