How far does one table, one query go?

Question

I'm doing a MySQL to Cassandra conversion. The fundamentals of the dataset are a master table of size ~10^7 rows to which several subsidiary tables are linked by foreign key. The subsidiary tables are up to size 10^10 rows.

The master table is really a set of seven columns, and it could be interpreted as a seven element partition key into which each 'row' in a subsidiary table could be assigned. These seven columns account for 99% of what is used with the WHERE clause for all SELECTS from the MySQL database. So far so good for conversion to the Cassandra schema.

Each of the seven columns in the master table has no more than 10,000 possible values, so each would seem to be a good partition key on its own. From reading about Cassandra database setup best practices, many people suggest a one table, one query approach. In this case, it would indicate that I should make seven tables, each using a single column of the master table as its primary key.

This would end up replicating many columns of data seven times over each. That this is the optimum strategy is something that my RDBMS wired brain can't seem to accept. For optimum read performance, should I replicate all columns of data into seven tables, one for each indexed column for searching?

Answer 1

The “one table per query” thing is a misnomer - the real practical advice is to figure out what you need to read, and then store data to serve that purpose

Sometimes that means fully denormalizing your data, duplicating columns between tables

Sometimes that means lookup tables that act as manual indices - whether you denormalize or use a lookup table depends on which is easier for you, the extra query or the extra storage cost. That’s a personal decision.

10,000 items in a partition is probably fine unless their large blobs (try not to hit 100mb/partition), but do try to take advantage of clustering for queries where you’ll fetch multiple rows.