MongoDB. [Key Too Large To Index]

Question

Some Background: I'm planning to use MongoDB as the publishing frontend db for a few of my websites. The actual data will be kept in a SQL Server db and there will be background jobs that will populate the MongoDB at predefined time intervals for readonly purposes to boost website performance.

The Situation: I have a table 'x' that i translated into a mongo collection, everything worked fine.

'x' has a column 'c' that was originally a NVARCHAR(MAX) in the source db and has multilingual text in it.

When I was searching by column 'c', mongo was doing fullscan on the collection.

So I tried doing an ensureIndex({c : 1 }) which worked but when I checked the mongodb logs it showed me that 90% of the data could not be indexed as [Key Too Large To Index] !!

And thus is has indexed 10% of the data and now only returns results from that 10% !!

What are my alternatives ??

Note: I was using this column to do full text searching in SQL Server, now im not sure if I should go ahead with Mongo or not :(

Adenine

Answer 1

if you need to search text inside a large string you can use one of those:
keyword splitting
regular expression

the former has the downside that you need some "logic" to combine the keyword to make a search, the latter heavily impacts on performance.
probably if you really need full text search the best option is to use an external indexer like solr or lucene.

Adinaadine

Answer 2

Try to run your mongod process with this parameter:

sudo mongod --setParameter failIndexKeyTooLong=false

And than try again.

Varini

Answer 3

Since you can do some elaboration, you could extract some key words and put them in a field:

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

and make an index on that.

Pointsman

Interesting, so that means I can use something like lucene to breakup the text into keywords and put them in an array field and index that field? – Adenine

yes, but if put all the words in the array field you will face the same problem – Pointsman

Answer 4

Don't use mongo for full text searching

its not designed for that. Yes obviously you will get an error key too large on indexing for long string values.

Better approach would be using full text search servers (solr/lucene or sphinx) if your main concern is search.

Greathouse

Answer 5

Recent (2.4 and above) MongoDB builds provide a couple other options:

1. As the OP's stated desire is for full text search, the right approach would be to use a text index which directly supports that use case.
2. For an exact match index on long string values you can use a hashed index.

Grannie