I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be sorted by relevancy.
I'm trying to avoid going for google custom search, amazon aws search, google app search or any hosted service like sphinx, solr etc. The only reason I'm trying to avoid this is it means my costs and complexity is kept right down.
With this fairly low amount of entires is it possible to still keep high performance using MySQL Fulltext search? It seems possible to still get reasonable performance, especially when the amount is documents is small. Normally comparisons are done against millions of entries, not thousands.
I guess my question is, it is possible to get reasonable full text searching using MySQL on simple shared hosting on that amount of documents or am I still asking too much without bringing in proper search engine software.
Best Answer
I often query a 170K-row table using InnoDB FULLTEXT. (Avg: 300 words.) I typically get the answer of a few dozen rows in under 200ms. That 200ms drops to below 20ms if the cache is warm.
I don't think the number of documents in the table matters as much as the number of documents returned. If you need to fetch the text for 100 documents (after searching the index), that could take a full second to randomly fetch from a cheap disk.
Beware of benchmarks; they may be delivering only document ids, not the text; and they may be assuming everything is cached in RAM. Since you have only a few MB, the caching is a valid assumption for you.