I'm creating a search engine and needs to search keywords in a text field (about 300 words in each text field, unstructured).
I'm thinking about noSQL\MySQL+Sphinx, the performance impact here is crucial.
which DB engine should I use?
5
-
1The database platform you know and/or have will be best if it is sufficient. Just because the performance is crucial doesn't mean it needs to be the fastest solution, only fast enough. Maintainability, extensibility, and cost are other factors that should be balanced carefully with performance.– Leigh RiffelCommented Jun 13, 2012 at 12:43
-
I don't know what will be sufficient, I can learn any engine needed for the cause, that's why I'm asking here first.– YSYCommented Jun 13, 2012 at 12:52
-
Then pick one you want to learn and try it. Become an expert on that platform and it will either work or you will know that it can't work and can move on to another platform. Perhaps those with experience doing text searches of this magnitude can answer the question for a limited set of platforms, but determining the best would require a massive test with experts for every platform. Perhaps a performance goal would allow those with experience to tell you which platforms would meet your requirements.– Leigh RiffelCommented Jun 13, 2012 at 13:04
-
I can't say it is fastest (haven't done a comparison), but IBM supports full text search with their DB2 Text Search "add-on" to DB2. (I say add on because like everything IBM likes to charge for additional features.) But I mention it because it is an option and we have considered it.– Chris AldrichCommented Jun 13, 2012 at 15:10
-
Why not separate it from the RDBMS and use something like Lucene? It wicked fast, but you're going to need to maintain your own indexes.– swasheckCommented Jan 4, 2013 at 5:50
Add a comment
|
1 Answer
First, relational systems are not really all that great for full text search. PostgreSQL's full text search isn't bad as things go but it has important limitations. For example you cannot search for neighboring words. So you are going to have to break this part out to another system. You are far better off just getting a dedicated system for that part. Most of the RDBMS-based solutions tend to be good enough to use when using other filters as well for general db queries, but that's not the same thing as being good enough on their own.
The second thing to keep in mind is that this is a specific type of search query. Get a dedicated engine to handle it.
That doesn't mean ditch the RDBMS though. You very likely are going to need to track things which an RDBMS does a good job at and if you go with a fully non-relational approach you will paint yourself into corners. NoSQL means saying "No, you can't run an ad hoc report aggregating the data in that way because we aren't storing it in that way." In general you want to be storing everything except the actual documents and full text indexes in an RDBMS.
