Up until today I've used
LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how full text index works.
I have an API that returns categories. On the front-end I have an input box that auto-completes the input as the user types. So assuming I have the following categories:
- House keeping
- Entertainment and recreation
- Taxes and bills
When the user types "ta" I'd like to offer them both "Entertainment and recreation" and "Taxes and bills". Needless to say that I've discovered that full text index wont return the given result on the term "ta". Looks like full text index works on actual text like "tax" or "bill" and not on meaningless phrases like "ta" or "ou".
Am I using fulltext index incorrectly and I should use the old good (bad?)
LIKE %ta%? Or are there any other ways to achieve described functionality that would also be db efficient (consider the fact the the front-end will query the DB on each character the user inputs).
For references: I'm using PostgreSQL and my query looks like
select * from categories where to_tsvector('english', name) @@ plainto_tsquery('english', 'ta')
Take a look at Amazon's search. I type a phrase like "hou" and it offers me results like "house decor" "house of cards" "housewarming". How such search performed? By a regular index on the column +
I know there are solutions like elastic search of sphinx. I don't know if they are suited in my case, but for now I want to stay inside the database to minimize costs and time in favor of faster development.