Which database engine is best for full text search on a 10M rows table

database-recommendationfull-text-search

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?

Best 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.

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Native full-text can handle all of the features you've mentioned.

Ranking/relevance, weighting
Word inflection and expansion
Customisable noise words and thesaurus

Whether or not it will be the best solution for your particular domain is a tough call. Rather than attempt to hand-roll an alternative, spike a proof of concept using SQL FTS and repeat the exercise with one or more open source alternatives.

Lucene is the obvious contender to compare and comes with the "Stack Overflow search is now 81% less crappy" endorsement of the SO team.

Sql-server – SQL Server 2005: Full-text search space requirements

Here is some old code I wrote for SQL 2000. It still works on SQL 2005. You can use this to come up with a rough estimate of how much space you are using per document, then multiply that by your estimated number of documents.

   select 'CatalogName'   = left([name],30),
          'Status'        = case(FULLTEXTCATALOGPROPERTY ([name],'populatestatus'))
                              when 0 then 'Idle'
                              when 1 then 'Full population in progress'
                              when 2 then 'Paused'
                              when 3 then 'Throttled'
                              when 4 then 'Recovering'
                              when 5 then 'Shutdown'
                              when 6 then 'Incremental population in progress'
                              when 7 then 'Building index'
                              when 8 then 'Disk is full.  Paused.'
                              when 9 then 'Change tracking'
                              else 'Unknown'       
                            end,
          'ItemCount'     = FULLTEXTCATALOGPROPERTY (name,'itemcount'),
          'IndexSize(MB)' = FULLTEXTCATALOGPROPERTY (name,'indexsize'),
          'UniqueWords'   = FULLTEXTCATALOGPROPERTY (name,'uniquekeycount'),
          'ErrorLogBytes' = FULLTEXTCATALOGPROPERTY (name,'logsize'),
          'Location'      = left(s.path,50) 
   from sysfulltextcatalogs s

Best Answer

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Sql-server – SQL Server 2005: Full-text search space requirements

Related Question