Take the 2-minute tour ×

I'm building an index of data, which will entail storing lots of triplets in the form (document, term, weight). I will be storing up to a few million such rows. Currently I'm doing this in MySQL as a simple table. I'm storing the document and term identifiers as string values than foreign keys to other tables. I'm re-writing the software and looking for better ways of storing the data.

Looking at the way HBase works, this seems to fit the schema rather well. Instead of storing lots of triplets, I could map document to {term => weight}.

I'm doing this on a single node, so I don't care about distributed nodes etc. Should I just stick with MySQL because it works, or would it be wise to try HBase? I see that Lucene uses it for full-text indexing (which is analogous to what I'm doing). My question is really how would a single HBase node compare with a single MySQL node? I'm coming from Scala, so might a direct Java API have an edge over JDBC and MySQL parsing etc each query?

My primary concern is insertion speed, as that has been the bottleneck previously. After processing, I will probably end up putting the data back into MySQL for live-querying because I need to do some calculations which are better done within MySQL.

I will try prototyping both, but I'm sure the community can give me some valuable insight into this.

share|improve this question
What is your insertion model? Random, batched, in-order? Are there keys that should be turned off during batch insertion? Be sure to use insert table (...) values (), (), (), (), (), ()... (), (); so you don't parse as many statements, let the text grow to about the size of the mysql server channel limit. –  Don Nov 7 '09 at 2:32
Insertion will be random: I will be scanning through hundreds of thousands of files, producing hundreds of tuples per document. No ordering (but I want to get ordering by weighted frequency out the other end). In my previous MySQL implementation, yes I was splitting the input into batches as per your suggestion. I can't remember the size, but I experimented to get the right kind of size. I also did delayed inserts because at insertion time I don't care about the data being available in real time. –  Joe Nov 7 '09 at 9:18
After looking at MapReduce, this looks like the kind of problem I could solve differently (i.e. not continually writing into a table but running a single batch process) but perhaps better. –  Joe Nov 7 '09 at 9:19
add comment

2 Answers

up vote 1 down vote accepted

Use the right tool for the job.

There are a lot of anti-RDBMSs or BASE systems (Basically Available, Soft State, Eventually consistent), as opposed to ACID (Atomicity, Consistency, Isolation, Durability) to choose from here and here.

I've used traditional RDBMSs and though you can store CLOBs/BLOBs, they do not have built-in indexes customized specifically for searching these objects.

You want to do most of the work (calculating the weighted frequency for each tuple found) when inserting a document.

You might also want to do some work scoring the usefulness of each (documentId,searchWord) pair after each search.

That way you can give better and better searches each time.

You also want to store a score or weight for each search and weighted scores for similarity to other searches.

It's likely that some searches are more common than others and that the users are not phrasing their search query correctly though they mean to do a common search.

Inserting a document should also cause some change to the search weight indexes.

The more I think about it, the more complex the solution becomes. You have to start with a good design first. The more factors your design anticipates, the better the outcome.

share|improve this answer
add comment

MapReduce seems like a great way of generating the tuples. If you can get a scala job into a jar file (not sure since I've not used scala before and am a jvm n00b), it'd be a simply matter to send it along and write a bit of a wrapper to run it on the map reduce cluster.

As for storing the tuples after you're done, you also might want to consider a document based database like mongodb if you're just storing tuples.

In general, it sounds like you're doing something more statistical with the texts... Have you considered simply using lucene or solr to do what you're doing instead of writing your own?

share|improve this answer
Thanks, I'll follow those suggestions up. FYI I'm not working on text, but it's a good analogy to use. –  Joe Nov 22 '09 at 17:09
add comment

Your Answer


