CODEWITHSUNDEEP
full-text-searchfull-text-indexing
user1950164
user1950164

Reputation:

How do websites do fulltext search and sort?

How do websites implement search and sort? (example: ecommerce search for a product and sort by price)

I've been wrestling with this for a while. I'm using MySQL and after long discussions here it seems that MySQL can't handle this. I've also asked here here whether posgres can do this and again it seems like the answer is no.

So how do websites do it?

EDIT: To be clear, I'm asking how websites do it in a way that uses both fulltext search and some sort of BTREE index for the sorting. To do fulltext search and sort without using one of the indexes would be easy (albeit slow).

Upvotes: 0

Views: 515

Answers (1)

StrayCatDBA
StrayCatDBA

Reputation: 2880

I worked for a large ecommerce site that used SQL Server full-text search to accomplish this. Conceptually, the full-text search engine would produce a list of ids, which would be joined against the b-tree indexes to return sorted results. Performance was acceptable, but we pushed it as far as we could go with the largest hardware available at the time (80 cpu, 512 GB RAM, etc). With 20-25 million documents a simple full-text query (2-3 terms) would have response times in the 3-5 second range. That was for the historical data. The live data set (around 1 million documents) would average 200ms with a wide distribution. We were able to handle 150-200 queries per second.

We eventually ended up moving away from SQL Server for search because we wanted additional full-text functionality that SQL Server didn't offer, specifically highly tunable relevance sorting for results. We researched various options and settled on elastic search hosted on aws.

Elastic search offered substantial improvements in features. Performance was great. We went live with 4 xlarge instances on aws. Query response times were right around 150-175 ms, and very, very consistent. We could easily scale up/down the number of nodes to keep performance consistent with varying amounts of load.

SQL Server was still the system of record. We had to develop several services to push changes from SQL Server to ES (incremental loading, bulk loading, etc). Translating the SQL search logic to ES was straight forward.

In conclusion, if your database can't meet your search needs, then use a tool (elasticsearch) that does.

Upvotes: 2

Related Questions