5 ms·
It's curious they omitted MySQL, which is the usual point of comparison for Postgres. > InnoDB full-text search is modeled on the Sphinx full-text search engin
by vosper 4y ago
It's curious they omitted MySQL, which is the usual point of comparison for Postgres.
> InnoDB full-text search is modeled on the Sphinx full-text search engine, and the algorithms used are based on BM25 and TF-IDF ranking algorithms. For these reasons, relevancy rankings for InnoDB boolean full-text search may differ from MyISAM relevancy rankings.
> InnoDB uses a variation of the “term frequency-inverse document frequency” (TF-IDF) weighting system to rank a document's relevance for a given full-text search query. The TF-IDF weighting is based on how frequently a word appears in a document, offset by how frequently the word appears in all documents in the collection. In other words, the more frequently a word appears in a document, and the less frequently the word appears in the document collection, the higher the document is ranked.
MySQL's FTS is fine. We're using it at work for fairly basic boolean searches on millions of documents, also retrieving the relevancy score, and it's plenty fast. We'll outgrow it one day, but for now it's pretty easy and does well enough.
https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.htm...
- sandinmyjoints 4y agoAgreed that MySQL FTS is "fine". Have you run into issues with the default value of innodb_ft_result_cache_limit (2 GB)? That's bit us (OOM restarts) a couple times.
- sroussey 4y agoBTW: when you outgrow it, it will be sudden. It’s performance fails in a very non-linear way. Edit: keep the index in memory
- mark_l_watson 4y agoI thought the universal practice for getting good performance for relational databases, RDF, other graph, etc. data stores is to be able to hold indices in memory.
- sroussey 4y agoYes, but paged b-trees are more graceful when they can’t fit in memory—which was the default when I cut my teeth on databases. It’s also a reason why MyIsam separated data and index files. InnoDb is a clustered index, so all the data is packed next to the primary key.