MongoDB. [The key is too large for indexing]

Question

Some details: I plan to use MongoDB as the external db interface for several of my websites. The actual data will be stored in the SQL Server db database and background jobs will run that will populate MongoDB at predefined time intervals for readonly purposes to improve site performance.

Situation: I have a table "x" that I transferred to the mongo collection, everything worked fine.

'x' has a column 'c', which was originally NVARCHAR (MAX) in the original db and contained multilingual text.

When I searched for the 'c' column, mongo did fullscan in the collection.

So I tried to execute makeIndex ({c: 1}), which worked, but when I checked the mongodb logs, it showed me that 90% of the data could not be indexed as [The key is too large for the index] !!

And so he indexed 10% of the data and now only returns the results from that 10% !!

What are my alternatives?

Note: I used this column for full-text search in SQL Server, now I'm not sure if I should go ahead with Mongo or not :(

Answer 1

if you need to find text inside a large line, you can use one of them:
keyword splitting
regular expression

the former has the disadvantage that you need some “logic” to combine the search keyword, the latter greatly affects performance.
probably if you really need a full-text search, the best option is to use an external indexer such as solr or lucene.

Answer 2

Try executing your mongod process with this parameter:

sudo mongod --setParameter failIndexKeyTooLong=false 

And try again.

Answer 3

Since you can do some work, you can extract some keywords and put them in a field:

  _keywords : [ "mongodb" , "full search" , "nosql" ] 

and create an index for this.

Answer 4

Do not use mongo for full-text search

it is not intended for this. Yes, obviously, you will get an error when indexing is too large for long string values.

The best approach would be to use full-text search engines (solr / lucene or sphinx) if the main problem is search.

Answer 5

The recent ( 2.4 and up ) MongoDB creates several other options:

• As the OP stated for full-text search, the correct approach would be to use a text index that directly supports this use case.
• For an exact index to match long string values, you can use a hashed index .