(Willing to PR) In sharding, selectively broadcast to the databases that contain the data, based on non-sharding key?

[fzyzcjy]

Feature Request

Related to: #24478

Hi, thanks for the package! I wonder whether it is possible to selectively broadcast to the databases that contain the data, based on non-sharding key?

Again using the dummy example in the last issue: Suppose we have a message table with columns chat_id, message_id, another_id, with chat_id being the sharding key. Then, we have a SQL like SELECT ... WHERE another_id IN (100, 200, 300) AND .... Suppose, for simplicity, we have 64 shards, and suppose we know another_id=100,200 is in shard A and another_id=300 is in shard B. Then, ideally, shardingsphere should only send (modified) query to shard A and B, instead of all 64 shards (which is very expensive). So, is this possible? Thanks.

Is your feature request related to a problem?

see above

Describe the feature you would like.

see above

[tristaZero]

@fzyzcjy Hello, sorry for the late response. I'm interested in this one, my question is ,

200 is in shard A and another_id=300 is in shard B

How do you know that?

[fzyzcjy]

@tristaZero Hi, I know it by: Create an extra table, say, message_extra with column another_id and chat_id, and another_id is sharding key. Then, when I want to query SELECT FROM message WHERE another_id IN (100,200,300), I will firstly execute SELECT ... FROM message_extra WHERE another_id IN (100,200,300). By doing this extra query, I know 200 is in shard A and another_id=300 is in shard B.

[tristaZero]

That means we have to update this index table together whenever there is DML, right? Perhaps we should also use the XA protocol to guarantee this transaction involves a data table and an index table, correct?

[fzyzcjy]

That means we have to update this index table together whenever there is DML, right?

Yes, IIRC, that's how Vitess does it

Perhaps we should also use the XA protocol to guarantee this transaction involves a data table and an index table, correct?

Yes, again Vitess seemed to do so. Not remember exactly though.

Btw maybe we should make XA optional for this (e.g. users may disable it in non-critical data), because it reduces the performances a lot.

[fzyzcjy]

(For future readers: @tristaZero also has some discussion in ShardingSphere's public Slack channel around https://apacheshardingsphere.slack.com/archives/C026MLH7F34/p1678691279002849)