Drupal 7.x

The search module's indexing maintenance sql query takes a couple of minutes to complete each time cron gets triggered.

SELECT t.word AS realword, i.word FROM search_total t LEFT JOIN search_index i ON t.word = i.word WHERE i.word IS NULL`

Database search info data info:

127k nodes

37m search_index

1.3m search_total

Search settings: 100 Number of item to index per cron ron

Indexing settings: 2 Minimum word length to index

Search is only enabled for nodes and once indexed, searches are fairly fast. However the actual process of re-indexing takes it's toll in our database server. Do you guys have any suggestions on how to mitigate this problem caused by extremely large websites? How do large websites scale Drupal's built-in search functionality without using additional tools like solr, elatisticsearch, etc.? All indications I'm seeing is to probably throw more hardware at this problem.

  • search_cron() should only be indexing nodes that have changed. How many are changing per day? How often are nodes changing?
    – mpdonadio
    Jul 20, 2016 at 21:06
  • @MPD Average about 10-20. However the actual slow query has to LEFT JOIN the entire search_total and search_index. tables. api.drupal.org/api/drupal/modules!search!search.module/function/…
    – Alpha01
    Jul 20, 2016 at 21:10
  • Have you optimized your database to make your buffers are scaled properly (eg, did a baseline with mysqltuner)? Oddly, I just checked at a Drupal 7 instance and search_index.word isn't its own index, just part of the compound primary index. Hmmm.
    – mpdonadio
    Jul 21, 2016 at 0:14

1 Answer 1

  1. Maybe you should use a Search API and Search API database module? It's more flexible in terms of working with index. For example, you can configure index settings(which fields to index)and rebuild index after it.
  2. Try to use Google Custom Search module.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.