unepwcmc/SAPI — pull request #313 by agnessa (2014-05-07 09:14:03+00:00)
Title: Turbo auto complete

A rewrite of the auto complete mechanism that uses a more efficient query to fetch results (in my tests 7x faster than old version).

The query runs on a new underlying structure that is dedicated for this mechanisms and possibly of little use to other components - the auto_complete_taxon_concepts_mview. That table has one row per each taxon - name_for_matching combination, where name_for_matching may be any of scientific name, synonym or common name. The visibility flags that were previously in taxon_concepts_mview (show_in_checklist_ac etc) have been move to this new table. Otherwise the table only holds information required for the auto complete to work, to keep its size down.

There are indexes to support prefix matching on the name. Multi-word common names have been split into multiple rows, so that matching on 'eagle' will bring up 'golden eagle', but matching can use the prefix search rather than the un-indexable LIKE '%eagle'. There are no indexes on the ordering fields, as they didn't seem to be imrpving speed when I was testing in development, might be worth to revisit when deployed to production.