I was troubleshooting an issue with my drupal site where indexing seemed to take forever. After looking at the mysql processlist, I noticed a whole bunch of update/delete queries (see processlist below). You'll notice these queries are taking way too much time to run. Wouldn't it make more sense to create an index for entity_id? What's odd is some of the fuzzy search tables index on item_id while others do not. I suspect this is a bug.

I manually added indexes to the item_id on the tables that did not have the index and I noticed an immediate difference. WIth the indexes, there was at most only a single query in the processlist. Also, after the index task ends, the queued queries are cleared out within a few seconds.

| 23629 | d_corporatecarp | localhost:53533 | d_corporatecarp | Query | 5 | updating | DELETE FROM fuzzysearch_default_fuzzysearch_index_title WHERE (item_id = '2920') |
| 23631 | d_corporatecarp | localhost:53539 | d_corporatecarp | Query | 1 | updating | DELETE FROM fuzzysearch_default_fuzzysearch_index_search_api_viewed WHERE (word_id = '0') |
| 23633 | d_corporatecarp | localhost:53545 | d_corporatecarp | Query | 3 | updating | DELETE FROM fuzzysearch_default_fuzzysearch_index_title WHERE (item_id = '2909') |
| 23635 | d_corporatecarp | localhost:53550 | d_corporatecarp | Query | 2 | updating | DELETE FROM fuzzysearch_default_fuzzysearch_index_title WHERE (item_id = '2904') |

Comments

kristen pol’s picture

kristen pol
she/her
Primary language English
Location Santa Cruz, CA, USA
commented

There are some things that will help:

1) Make sure you have enough fuzzy search indexes enabled on:

[yourdomain]/admin/config/search/search_api/index/default_fuzzysearch_index/fields

You will need to reindex your fuzzy search index after this at:

[yourdomain]/admin/config/search/search_api/index/default_fuzzysearch_index/status

2) Depending on what you have chosen for #1, you can add db indexes on the item_id column for tables that don't have it. I did this by adding a update function in my custom module install file like:

function mymodule_update_7001() {
  if (db_table_exists('fuzzysearch_default_fuzzysearch_index_title')) {
    db_add_index('fuzzysearch_default_fuzzysearch_index_title', 'item_id', array('item_id'));
  }
  if (db_table_exists('fuzzysearch_default_fuzzysearch_index_field_featured_text')) {
    db_add_index('fuzzysearch_default_fuzzysearch_index_field_featured_text', 'item_id', array('item_id'));
  }
  if (db_table_exists('fuzzysearch_default_fuzzysearch_index_search_api_viewed')) {
    db_add_index('fuzzysearch_default_fuzzysearch_index_search_api_viewed', 'item_id', array('item_id'));
  }
}

Then run update.php (or drush updatedb) to have it create the table indexes.

3) The other thing I did was change the fuzzy search view to show 10 results instead of 20.

After these were done, the search became pretty fast (~1 to 2 seconds for most keywords).