help designing autocomplete response's

Question

I want my autocomplete to be very user friendly.

I have products that people will search for by:

part#
manufacturer

e.g. searches

HND123

Honda 123

So basically each manufacturer has a short form and a long form.

How should I build my search index so when the autocomplete needs to fetch the data from the db, it will return results for both types of queries?

Answer 1

I would do this as a UNION, even if you have the same table. Assuming that you are searching in two different columns.

SELECT PartNumber AS Term FROM Parts WHERE PartNumber LIKE 'input%'
UNION
SELECT Manufaturer AS Term FROM Parts WHERE Manufactuer LIKE 'input%