High Performance Database Opinion

Question

I'm developing software using a MySql database and Hibernate to access it. The problem I am having is when I look for 1 keyword I am using 40 000 queries already and the application that I am developing should be able to process multiple keywords.

So basically we are dealing with a database filled with String values and a lot of comparing has to be done. For now, using a filter I'm loading all possible matches in memmory and I compare them in the java code. This is highly recursive and slow.

So obviously MySql and most of all Hibernate are not the way to go. Could anyone please provide some information on which database would provide better performance. I'm looking into Hypertable, MongoDb, Hbase, Graph Database, ... but I'm not sure which way to go.

Please help. Thanks

Answer 1

Your real problem is your using 40,000 queries.

Can you explain your problem and process that leads to so many queries?

Regardless of what database you go with, your algorithm sounds too excessive and so it will always be slow.

Let's fix it first.

Answer 2

Perhaps I misunderstand your question, but ...

For now, using a filter I'm loading all possible matches in memmory and I compare them in the java code. This is highly recursive and slow.

Sounds like you're try to do the job of your database, in-memory? Create an index, write a better SQL query or something, but you're loading all possible matches and the iterating through them? At that point, why even use a database?

Basically, I don't think it's your choice of database (MySQL can handle much larger queries than 40,000 records with no problem). I think your algorithm needs some work.

Answer 3

Your approach is wrong, and you're doing something MySQL does natively - it can store the dataset in the RAM and work with it from there, which is what you're doing with your algorithm.

The other thing is that for specific things like text searching - there are known methods and various storage engines that are specialized for such purpose. For example, Sphinx is one of those.

Another thing is actually using some sort of data structure that makes searches quick, such as trie - which is incredibly useful for doing things such as autocomplete (this is just an example that doesn't have to be directly connected to your question - it's just a hint that there are known data structures that work fast with strings).

Also, why do you think a NoSQL solution would be quicker when it comes to comparing large volume of string data?

As others have pointed out - it seems your app design and algorithm are the ones that are the culprits here, not underlying technology. You should be more exact in your question and outline what it is that you're doing, how you're doing it and what you'd like for it to be doing. When you answer those questions, people might point you to right direction in solving your problem because it seems you took wrong approach.