We talked about the "right way" to make autocomplete work better a bit at the England and Netherland CiviCRM code sprints. There should be an autocomplete cache table with rows for all fields that we want to be searchable and displayed, which probably are:
This table should have a flattened column with all these fields concatenated, as well as a display field that shows what should be displayed in the dropdown.
There should be an api function to rebuild the entire autocomplete cache, which will truncate this table and for each contact where is_deleted=0 it should add rows to it. And there should be triggers that keep this table up-to-date every time a contact, email address, or address is added, edited, or deleted.
Then when people use the autocomplete search boxes, there can just be a single query done on the flattened column of this table. And it will be fast.
We talked about the "right way" to make autocomplete work better a bit at the England and Netherland CiviCRM code sprints. There should be an autocomplete cache table with rows for all fields that we want to be searchable and displayed, which probably are:
civicrm_contact.id civicrm_contact.display_name civicrm_contact.sort_name civicrm_email.email civicrm_address.city
This table should have a flattened column with all these fields concatenated, as well as a display field that shows what should be displayed in the dropdown.
There should be an api function to rebuild the entire autocomplete cache, which will truncate this table and for each contact where is_deleted=0 it should add rows to it. And there should be triggers that keep this table up-to-date every time a contact, email address, or address is added, edited, or deleted.
Then when people use the autocomplete search boxes, there can just be a single query done on the flattened column of this table. And it will be fast.