Join Log in
Score:0
avatar Drupal

Sorting on a custom date field is slower than sorting on relevance: Is there a solution?

pe flag
aharown07

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
38
0 + 0
9
sonfd avatar
in flag
sonfd
Is your date sort using the date data from your index? or just a regular date field? Make sure you're using the indexed date field data.
0
Reply
pe flag
aharown07
It's the indexed date field. Some other details from analysis so far: the render time is often worse if I do the search with relevance sort in the exposed filter first, then change it to date and apply. So far, that set of steps appears to be worse than doing the search with date in the dropdown first, but I'm less certain of that. Also: the number of total hits has a major impact. More = longer render. May help to know: the view doesn't include any entity references to slow things.
0
Reply
pe flag
aharown07
Another clue: If I use 'rendered html' in the exposed filter instead of 'full text search,' results sorted by custom date field (indexed) are much quicker. The rendered html filter doesn't offer relevance sorting though. Maybe I need to default to 'relevance sort & full text' but have a button to route the query to 'date sort & rendered html.' Would like to understand why this is happening though.
0
Reply
pe flag
aharown07
Might be related to this issue: https://www.drupal.org/project/search_api/issues/3227268 Much of the discussion there is beyond my current level of expertise, so I'm unsure of the relevance.
0
Reply
Score:0
avatar Drupal
pe flag
aharown07

This is a vague solution, because I wasn't able to determine why sort would behave this way.

I can report, though, that installing Solr and switching the index to the Solr backend resulted in there no longer being any noticeable difference with one sort vs. the other.

To clarify: same views and fields, and same index configuration. (In search api, you can switch an index's backend, then re-index). This resulted in very quick sorts using the same search result view pages.

0 + 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.