0

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 '16 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 '16 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 '16 at 0:14
0
  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, privacy policy and cookie policy

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