How to Query Sharded Tables: Single, Multi-Value and Cross-DB Joins

Querying Sharded Fields

Single‑value query SELECT * FROM table1 WHERE user_id = 'test1234'; When user_id is the sharding key, the routing algorithm computes the hash of 'test1234' to determine the target shard, then retrieves the record directly from that shard.

Example:

hash('test1234') = 2398283927 % 1024 = 531 → shard 3

Multi‑value query

SELECT * FROM table1 WHERE user_id IN ('test1234','papa17','abcd');

Each value is routed independently to its shard; the results from the relevant shards are then merged.

Example:

hash('test1234') = 2398283927 % 1024 = 531 → shard 3
hash('papa17') = 3339829221 % 1024 = 511 → shard 3
hash('abcd') = 55239822711 % 1024 = 130 → shard 1

Querying Non‑Sharded Fields

SELECT * FROM table1 WHERE name = 'dys';

Since name is not a sharding key, the query must be executed on every shard and the results merged, which is less efficient.

Cross‑Database Join Queries

SELECT * FROM table1 INNER JOIN table2 ON table1.user_id = table2.name;

Cross‑shard joins have no highly efficient solution; each shard must be queried and the results combined, which can be costly.

Pseudo‑code example

for row in (select * from table1) {
    add (select * from table2 where table2.name = row.user_id) to result;
}

In practice, middleware often only supports simple joins; complex cross‑shard queries are usually offloaded to search services such as Solr.