Query fanout distributes one logical query across multiple partitions or nodes. A global secondary index can trigger cluster-wide fanout because its partition key differs from the base table’s. section 7.3.3 section 7.4
Compare index choices by where their keys are stored:
- Global secondary indexes may query many nodes, creating expensive fanout for large queries. section 7.3.3
- Local secondary indexes share the base table’s partition key, keeping matched rows on the same node and avoiding cluster-wide fanout. section 7.3.3 section 7.4
- Both index types still require an index lookup followed by reads from the base table. section 7.4
Looking up an author
A query for an author by name can read a global index and then fetch matching rows from the authors table. If the index spans many partitions, the lookup fans out across the cluster; including the base table’s partition key enables a local index instead. section 7.3.3 section 7.4
Table 7.3 ScyllaDB creates a local secondary index when you create an index that shares the same partition key as its base table.
|
Index
|
Index type
|
Why?
|
|---|---|---|
CREATE INDEX ON authors(name)
|
Global
|
authors is partitioned by
id, which isn’t part of the index.
|
CREATE INDEX ON authors((id), name)
|
Local
|
authors is partitioned by
id, which is included in the index.
|
Compare the CQL definitions that make an authors index global or local by including the base table’s partition key. section 7.3.3
