-1

I have an existing APP that has N types of products that is normalized with MySQL. Each types of products have over 500K skus, and one of them are in the in 2M currently.

We are currently tracking on the individual skus in terms of CTR. Despite large data set we are able to run a Cron Job on PHP to do the calculation pre-hand and assign them to each skus. We require to sort the list when being pulled to be able to see in terms of CTR.

This eco system has a 5-7 table join with a very bulky query. If I ever want to sub-select the items and apply a sorting score on them - it is quite slow.

Our plan is to leverage this with Cassandra. Reasons:

  • We will be distributing the DB into different location, therefore the multi data center replication is a nice feature.
  • It is decentralized for no single point of failure as we require constant uptime
  • The Skus will be increasing
  • dumping/re-selecting needs to be fast, with large data set

the RMDBS will NOT be removed entirely as we might use it as a worse case backup.

My largest obstacle right now with Cassandra are the no-join, no order by (since everything is ordered by key for you), and mass update.

Top Queries I am doing currently in MySQL:

  • Retrieve all product from a given product type sorted by sorting score.
  • Retrieve all product from a given product type sorted by date first, then sorting score.
  • Retrieve all product from a given product type filtered by 1 to 5 criteria, show it ordered by sorting score.
  • Mass update on sorting score across a given product type.
  • Search for items according to X criteria and assign them a "tag". Each product element could have many tags.

Am I still going into the right direction in choosing Cassandra for my application to enable a faster read/write on a large scale of data.

If I want to expand into user tracking, calculation, and history look up - considering that I have a large user database will Cassandra be still the best approach (as far as tracking is concern, we do not wish to outsource it to an analytic API because we will be using this data to re-serve the content to the user - so pulling and crunch the data in house)

The only other DB that has an ease of transition would be FoundationDB however its still in Beta and the document is slim. The only good positive sign is the SQL Layer which removes a lot of the restriction of Cassandra in terms of transitioning and coming from a MySQL environment.

For those who are responding that Cassandra will achieve this please let me know if there are any limitation or question on all the different queries I will need to do.

2

My largest obstacle right now with Cassandra are the no-join, no order by (since everything is ordered by key for you), and mass update.

If your current data structure depends on joins (or aggregation) to pull off the queries you need you won't find a nosql option that includes these. You do have some options:

  1. Consider reviewing your current data structure and queries to determine if you can do things more efficiently. A lot of performance issues can be solved with more efficient execution.

Performance improvements of your SQL would be called commonly SQL Tuning.

A post at ToadWorld.com shows a great example where someone tweaked their code to pull the same results in a different way. This resulted in a query that was taking 1 1/2 hours to run being reduced to a <1 second query.

For a fairly decent overview of SQL tuning Oracles SQL Tuning Guide provides a good starting point. You can apply the basic principles their to your re


  1. Review figure out how you will get your data into a state that will function correctly in a non-relational database.

  2. If you really can't make your option more efficient (and need the benefits of a relational database) consider using a newsql option: such as voltdb.

  • Can you elaborate on "efficient" - what do you mean by efficient. What is considered Efficient. – azngunit81 Feb 10 '15 at 16:00
  • Certainly. I'll post a detailed example as soon as I have a moment. – user58340 Feb 10 '15 at 16:32
  • Added a little subsection in that points to a good example of SQL Tuning as well as an Oracle guide that is a great starting point. – user58340 Feb 10 '15 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.