Database optimized for searching in large number o

Question

Im am currently searching for an alternative to our aging MySQL database using an EAV approach. Current projects seem to have outgrown traditional table oriented database structures and especially searches in such database. I head and researched about various NoSQL database systems but I can't find anything that seems to be what Im looking for. Maybe you can help.

I'll show you a generalized example on what kind of data I have and what operations I want to execute on them:

I have an object that has a small number of META attributes. Attributes that are common to all instanced of my objects. For example these

DataObject Common (META) Attributes

• Unique ID (Some kind of string containing a unique identifier)
• Created Date (A date time showing creation time of the object)
• Type (Some kind of type identifier, maybe something like "Article", "News", "Image" or "Video"
• ... I think you get the Idea

Then each of my Objects has a variable number of other attributes. Most probably, many Objects will share a number of these attributes, but there is no rule. For my sample, we say each Object instance has between 5 to 20 such attributes. Here are some samples

Data Object variable Attributes

• Color (Some CSS like color string)
• Name (A string)
• Category (The category or Tag of this item) (Maybe we also have more than one of these?)
• URL (a url containing some website)
• Cost (a number with decimals
• ... And a whole lot of other stuff mostly being of the usual column types

References to other data is an idea, but not a MUST at the moment. I could provide those within my application logic if needed.

A small sample:

Image

• Unique ID = "0s987tncsgdfb64s5dxnt"
• Created Date = "2013-11-21 12:23:11"
• Type = "Image"
• Title = "A cute cat"
• Category = "Animal"
• Size = "10234"
• Mime = "image/jpeg"
• Filename = "cat_123.jpg"
• Copyright = "None"

---

Typical Operations

An average storage would probably have around 1-5 million such objects, each with 5-20 attributes.

Apart from the usual stuff like writing one object to database or readin it by it's uid, the most problematic operations are these:

• Search by several attributes - Select every DataObject that has Type "News" the Titel contains "blue" and the Created Date is after 2012.
• Paged bulk read - Get a large number of objects from a search (see above) starting at element 100 and ending at 250
• Get many objects with all of their attributes - When reading larger numbers of objects, I need to get every object with all of it's attributes in one call.

---

Storage Requirements

• Persistance - The storage needs to be persistance and not in memory only. If the server reboots, the data has to be at the same point in time as when it shut down before. No memory only systems.
• Integrity - All data is important, nothing can be ignored. So every single write action has to be securely stored. Systems (Redis?) that tend to loose something now and then arent usable. Systems with huge asynchronity are also problematic. If data changes, every responsible node should see that.
• Complexity - The system should be fairly easy to setup and maintain. So, systems that force the admin to take many week long courses in it's use arent really a solution here. Same goes for huge data warehouses with loads of nodes. Clustering is nice, but it should also be possible to get a cheap system with one node.

---

tl;dr

Need super fast database system with object oriented data and fast searched even with hundreds of thousands of items.

A reason as to why I am searching for a better alternative to mysql can be found here: Need MySQL optimization for complex search on EAV structured data

---

Update

Key-Value stores like Redis weren't an option as we need to do some heavy searching insode our data. Somethng which isnt possible in a typical Key-Value store.

In the end, we are using MongoDB with a slightly optimized scheme to make best use of MongoDBs use of indizes.

Some small drawback still remain but are acceptable at the moment: - MongoDBs aggregate function can not wotk with very large result sets. We have to use find (and refine our data structure to make that one sufficient) - You can not sort large datasets on specific values as it would take up to much memory. You also cant create indizes on those values as they are schema free.

Answer 1

I don't know if you wan't a more sophisticated answer than mine. But maybe i can inspire you a little.

MySql are scaleable and can be used for exactly your course. I think it's more of an optimization and server problem if you database i slow. Many system with massive amount of data i using MySql and works perfectly, Though NoSql (Not-Only SQL) is built for large amount of data with different attributes.

There's many diffrent NoSql providers and they have different ways of handling you data. Think about that before you choose a NoSql platform.

The possibilities are

• Key–value Stores - ex. Redis, Voldemort, Oracle BDB
• Column Store - ex. Cassandra, HBase
• Document Store - ex. CouchDB, MongoDb
• Graph Database - ex. Neo4J, InfoGrid, Infinite Graph

Most website uses document based storing, but ex. facebook are using the column based, because of the many dynamic atrribute.

You can try the Document based NoSql at http://try.mongodb.org/

In the end, it really depends on how you build and optimize you database, and not from which technology you choose, though chossing the right technology can save a bunch of time.

The system we have developed are using a a combination of MySql and NoSql depending on what data we are working with. MySql for the system itself and NoSql for all the data we import via API's.

Hope this inspires a little and feel free to ask any westions