In the original thread (https://www.drupal.org/node/1836992) where the autocomplete was added, it was only tested with about 200 nodes. I'm using this with at least 17000 nodes and it doesn't show autocomplete-suggestions after sometimes 7 seconds.

I traced it down to the actual query taking that long, because it was adding a list of item_ids (all 17k of them) for each search-field (3 fields) in a single query.

I made a patch to change this to doing the same thing with multiple temporary tables (1 for each search-field).
Now the suggestions come a lot faster.

CommentFileSizeAuthor
#5 2807111-5--autocomplete_multi_field_optimization--interdiff.txt746 bytesspadxiii
#5 2807111-5--autocomplete_multi_field_optimization.patch2.91 KBspadxiii
#3 2807111-3--autocomplete_multi_field_optimization.patch2.75 KBdrunken monkey
#3 2807111-3--autocomplete_multi_field_optimization--interdiff.txt1.59 KBdrunken monkey
#2 2807111-2.patch2.77 KBspadxiii

Comments

SpadXIII created an issue. See original summary.

spadxiii’s picture

spadxiii commented
Status: Active » Needs review
StatusFileSize
new 2807111-2.patch2.77 KB

The patch where I change the where-condition to using temporary tables.

Note that this needs some testing, but looks like it works fine for my case.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
StatusFileSize
new 2807111-3--autocomplete_multi_field_optimization--interdiff.txt1.59 KB
new 2807111-3--autocomplete_multi_field_optimization.patch2.75 KB

This is a good idea, thanks!
A revision of your patch with a few small corrections/nit-picks is attached.

However, two questions in addition:

  1. Wouldn't it be possible to create all temp tables after the first just with a SELECT * FROM $temp_table? If possible, that should be a lot faster than executing the complete search query multiple times.
  2. In any case, I wouldn't want to significantly decrease performance for small result sets. Either with or without the above optimization, could you test how this compares with the original method when there are just, say, 50 results? If this significantly decreases performance for that case, we might need to change the code depending on the total result count.
spadxiii’s picture

spadxiii commented

I can probably run some tests this or coming week. (If I don't forget :))

spadxiii’s picture

spadxiii commented
StatusFileSize
new 2807111-5--autocomplete_multi_field_optimization.patch2.91 KB
new 2807111-5--autocomplete_multi_field_optimization--interdiff.txt746 bytes

I've just tested it a bit with a clean drupal install and some generated nodes (with devel).
In the search index, I added the node title and body as full text and created a view with full-text search with autocomplete. (Using search_api_autocomplete).

With and without the patch the page execution times (devel setting) vary between 110 and 150 ms for 50 to 250 random records. Also the autocomplete request takes about 110 to 150 ms to complete (firebug timing)

The first search after cache clear seems higher in any case. After that, the timings are quite similar.
I also added a few lines to fill the temp tables with a "SELECT * FROM $temp_table" instead of the initial query. Execution times stay pretty much the same.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Needs review » Fixed

OK, thanks, sounds good!
Committed.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.