We are using Vitess database to scale and achieve Horizontal Sharding in mysql. is it possible to do the secondary shard in Vitess.
For eg: Table 1 - Agency ( AgencyID INT, CreatedOn DATETIME )
Table 2 - PayrollDetails ( AgencyID INT FOREIGN KEY TO Agency Table, PayrollID INT, PayrollCreatedOn DATETIME )
Now We sharded both the tables with AgencyID as a Sharding Key. but PayrollDetails table is very huge and it has more then 100 million of records. So now we are planning to shard PayrollDetails table again with the PayrollCreatedOn field and Primary Shard for both the tables should be with the Agency Key but payrollDetails table should shard with the both AgencyID and PayrollCreatedOn.How can we achieve it in Vitess?
Conceptually, the sharding key (primary vindex) is used to decide which shard a row goes to. So, it's not possible to have two sharding keys because they would dictate conflicting locations for the row.
If I understand correctly, you want to query the table using PayrollCreatedOn in the where clause, you can create a secondary Vindex. This will create a lookup table that points at where the row lives, and Vitess can exploit that. There's an explanation for this here: https://vitess.io/docs/reference/vindexes/. There is a new command called CreateLookupVindex that is capable of backfilling this lookup table. It's yet to be documented, though.
Vitess also lets you "materialize" a table by using a different primary vindex. In that case, the second table will be a real-time copy of the first table, but sharded differently. You can see a demo for this on the vitess front page (scroll down to the video).