1

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.

enter image description here

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
1

MySQL is completely capable of serving as your full text search engine. InnoDB FTS indexes are reasonably good. With the size you described it should all fit in memory but of course that depends on the other tenants on the shared hosting.

If it's not available (you mentioned shared hosting) you can implement your own pretty easily. Full text search is just basically inverted indexing. This way you can also have any custom trickery over it you want in your application which might not be possible with InnoDB FTS. You can see a basic implementation of this and performance + overhead comparison here: http://charlesnagy.info/it/mysql/full-text-search-in-mysql

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.