Scalable searching algorithm SQL

Question

So I have a list of users stored in a postgres database and i want to search them by username on my (Java) backend and present a truncated list to the user on the front end (like facebook user search). One could of course, in SQL, use

WHERE username = 'john smith';

But I want the search algorithm to be a bit more sophisticated. Beginning with near misses for example

"Michael" ~ "Micheal"

And potentially improving it to use context e.g geographical proximity.

This has been done so many times I feel like I would be reinventing the wheel and doing a bad job of it. Are there libraries that do this? Should this be handled on the backend (so in Java) or in the DB (Postgresql). How do I make this model scalable (i.e use a model in which complexity is easy to add down the road)?

WHERE username = "john smith" is invalid standard SQL (String constants are specified using single quotes in SQL, double quotes are for identifiers). Which DBMS are you using? Queries like that are typically done using the full text search feature of the underlying DBMS. — user330315
– user330315, Commented Dec 26, 2016 at 0:01
Edited the double quotes. I am using Postgres (Heroku default) — Kafros
– Kafros, Commented Dec 26, 2016 at 0:26

lexicore · Accepted Answer · 2016-12-26 00:12:38Z

1

A sophisticated algorithm will not magically appear, you have to implement it. Your last question is whether you should do it in Java or in the database. In overwhelming majority of cases it's better to use the database for queries. Things like "Michael" ~ "Micheal" or spatial queries are standard features in many modern SQL databases. You just have to implement the appropriate SQL query.

Another point is, however, if SQL database is a right tool for "sophisticated queries". You may also consider alternatives like Elasticsearch.

answered Dec 26, 2016 at 0:12

lexicore

43.9k17 gold badges142 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kafros Over a year ago

What type of queries handle the (for lack of a better word) "near miss" queries?

lexicore Over a year ago

@Kafros You should move from binary logic to calculating the "score" of a match. However this is quite hard with SQL, really check Elasticsearch or similar products.

Collectives™ on Stack Overflow

Scalable searching algorithm SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related