Site setup:

- 1 node type being searched
- have about 60k nodes
- using View with exposed filters and specifically the 'Search: Search terms' filter
- results are paged

search on the word "land":
- result set is about 370 results (paged)
- results returned in under 2 sec

search on work "agreement":
- result set is about 9700
- results returned in about > 5 minutes (yup... a minutes)

at first i assumed this was deficiency in the the standard Drupal search which i thought was at the core of Views search, but when i do the same search with the std Drupal search page i get 977 pages of results in about 2 sec.

so:

- does this sound like the expected result?
- am i configuring something incorrectly?
- is there a different search plugin for Views?

CommentFileSizeAuthor
#2 views-Rulings_search.txt34.69 KBliquidcms
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dawehner’s picture

There might be many different kind of reasons for this.

* First how many nodes do you display on this view?
* Please provide an export, perhaps you do something stupid here, which could be the case.
* In general if you want to have a real fast search you can't get around using something like apachesolr.
Therefore in d6 the apachesolr module seems to the thing to use. You can also use views with it.

liquidcms’s picture

FileSize
34.69 KB

thanks for the reply,

we display 25 items per page - that isn't the issue

not looking for a "real fast search" but going from 2 sec to 5 minutes when result count goes from 400 to 2000 seems a bit likely something is being done wrong.

i know about solr and i mostly knew i could use it with Views... but have been trying to avoid having to set it up.. if someone comes back and tells me there is a major degradation over std Drupal search when using Views; then solr will be our our next approach.

export of the View is attached - it is the display "Content pane"

also, i have tried removing all the other exposed filters and the sorts - had no impact.

liquidcms’s picture

Title: Views search is extremely slow if numerous results are found » Views search performance degrades exponentially with number of results found
Category: support » bug
Priority: Normal » Major

the sql created by the search terms filter has this group by clause:

 GROUP BY search_index.sid, nid_1

i think the double group by causes the exponential decay in performance - any idea what it is used for?

if i remove that term in the group by; results are returned in 2-3 sec (as opposed to 6 minutes).

i need to review the returned results and see how much different with/out that clause.

i am trying to use devel module to see what the sql looks like for the std drupal search but no luck yet.

also, i am sure someone will say something like this isnt a bug because i should use solr.. but.. switching to a bug anyway.

Damien Tournoud’s picture

$handler->display->display_options['query']['options']['distinct'] = TRUE;

^ The GROUP BY is like ly there to satisfy this.

liquidcms’s picture

so doing a _views_query_alter and removing the search_index group by clause and then doing a search that returns 23k results.. now takes 3 sec; compared to over 10 minutes (and doing 2 searches of this size at same time crashes our server)

i reviews the first page and last page of the returned results and seem to have the same results.

i am sure there is a reason for the double group by - but i like the 3 sec version better :)

dawehner’s picture

This groupby is there because the search.module has a similar kind of approach to this issue

  // Build query to retrieve results.
  $select = "SELECT i.type, i.sid, $columns2 FROM {search_index} i $join $join2 WHERE $conditions GROUP BY i.type, i.sid HAVING COUNT(*) >= %d";
  $count_select =  "SELECT COUNT(*) FROM ($select) n1";
  $arguments = array_merge($arguments2, $arguments1, array($query[4]));

Personally i think that you can't really use search.module to have a scalable search solution.

liquidcms’s picture

i did a query alter to remove the 2nd groupby.. search is now screaming fast... only about 70k nodes but a reasonably popular federal government (CDN) website and been live for 2 months and no reports of any issue from removing that clause.

likely worth closing this as i can't see it ever being fixed.. just posted originally to see if anyone else stumbled upon this.. not sure why no one has.. maybe everyone with a site this size sees search doesn't work; thinks it is a flaw in drupal search or limits on sql server and goes through the pain of moving to solr. solr really isn't necessary until site hits 100's of 1000's of nodes.. just need to get rid of that groupby.. :)

dawehner’s picture

I'm somehow consider this as working as expected, as it just reproduces the behavior of drupal core.
In general i would consider using solr for every drupal site with search enable, even the drupal tables used to keep track of the search index in solr are much better designed.

MustangGB’s picture

Issue summary: View changes
Status: Active » Closed (won't fix)