I currently use MySQL and Sphinx to manage a pretty basic database containing ne

Question

I currently use MySQL and Sphinx to manage a pretty basic database containing nearly 350,000,000 records. It is expected to hit a billion records in less than 200 days (anywhere from 2000 to 5000 records per minute). MySQL is dreadfully slow and Sphinx improves the speed quite a bit, but delta indexing is a pain and I'm not sure if RT indicies are an option.

The data consists of several integer values and one float value. We search through the database based on integer matches and integer ranges (like match maybe 4 integer columns exactly, and we'll give another integer column a range between x and x+20000000).

Ideally, the best software would be free (in both freedom and free-beer), easy to configure, and returns searches quickly (<0.5 seconds). It should also have libraries available for NodeJS or any other scripting language (not really required).

Explanation / Answer

Two suggestions: 1) Oracle Database 11gR2, 2) MongoDB.

1) Oracle Database 11gR2:

The most advanced and the most powerful DBMS for transaction-heavy solutions. Can be downloaded and used for free (Support is commercial).

I've tested it on an ordinary laptop with a dataset of approximately 90M records. Searches based on the Id field(which was Integer) were returned within 0.1s.

2) MongoDB:

The most popular NoSQL-Based DBMS which has a great capability of integration with Node.Js. In fact, it's a Document-Based DBMS suitable for the Big Data.

Which way to go?

1) If you want the feeling of working with a RDBMS(just like MySQL) and if your search is based on integer values(You should create wisely indexes for those columns).

2) If integration with Node.Js is important for you and your data will grow unstructured

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.