I am working hierarchical data, as in the tree structure. i want to know what is the best way to store them in database.
I started with adjacency list, in MySQL. But the performance seems to dip as the data is increasing. I have around 20,000 rows stored in a MySQL table with parent child relationship and will increase in future. Fetching data is taking very long time as I have to write many self joins depending upon the depth of the tree.
So I was searching for best way to store this kind of data. In once place I found Nested Sets is better way than adjacency lists. Then I was advised to look upon NoSQL, if that would solve my problem. So I am confused now whether to remain in SQL or go into No SQL or if there is any other best way to handle this kind of data.
So can anyone suggest me what is the best way??
If MySQL is giving you more troubles than it solves, I'd take a look at MongoDB, CouchDB or ElasticSearch (depending on your use case). Maybe even Neo4j. Your choice should come down to several points such as replication, scaling capacity, consistency... I advise you to read carefully some official documentations before you decide. Here's a starting point for comparison.
Going NoSQL will get rid of all the joins and improve your performance but you'll still need to implement a proper hierarchy using adjacency list, nested sets, materialized paths and such...
Keep in mind NoSQL technologies above pretty much all use eventual consistency, which essentially means that your data might not be consistent at a given time among some nodes. If this is a problem you should stick to RDBMS.