User Tracking For Large Data Set with least amount of read/write

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.