How are join's handled with Cassandra?

Q: How are joins handled with the Cassandra storage engine?

A: Joins are only possible on the mysqld side. That is, if we want to compute a join between two tables, we will do:

1. Read the relevant data for the first table.
2. Based on data we got in #1, read the matching records from the second table.

Either of the tables can be an InnoDB table, or a Cassandra table. In case the second table is a Cassandra table, Cassandra Storage Engine allows to read matching records in an efficient way.

Some general info

All this is targeted at running joins which touch small fraction of the tables. The expected typical use-case looks like this:

• The primary data is stored in MySQL (ie. in InnoDB)
• There is also some extra data stored in Cassandra (e.g. hit counters)
• The user accesses data in MySQL (think of a website and a query like:

select * from user_accounts where username='joe')

Cassandra SE allows to grab some Cassandra data, as well. One can write things like this:

select 
  user_accounts.*, 
  cassandra_table.some_more_fields
from 
  user_accounts, cassandra_data 
where 
  user_accounts.username='joe' and
  user_accounts.user_id= cassandra_table.user_id

which is much easier to do than to use Thrift API.

If the user wants to run huge joins that touch a big fraction of table's data, for example:

"What are top 10 countries that my website had visitors from in the last month"?

or

"Go through last month's orders and give me top 10 selling items"

then Cassandra Storage engine is not a good answer. Queries like this are answered in two ways:

1. Design their schema in Cassandra in such a way that allows to get this data in one small select. No kidding. This is what Cassandra is targeted at, they explicitly recommend that Cassandra schema design starts with the queries.
2. If the query doesn't match Cassandra's schema, they need to run Hive (or Pig), which have some kind of distributed join support. Hive/Pig compile queries to Map/reduce job which are ran across the whole cluster, so they will certainly beat Cassandra Storage Engine which runs on one mysqld node (you can have multiple mysqld nodes of course, but they will not cooperate with one another).

It should be possible to run Hive/Pig on Cassandra (although this is not implemented at the time of writing).