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?