[CASSANDRA-10214] Enable index selection to be overridden on a per query basis

• Type: New Feature
• Status: Open
• Priority: Normal
• Resolution: Unresolved
• Fix Version/s: 5.x
• Component/s: Legacy/CQL
• Assignee: Unassigned
• Reporter: Sam Tunnicliffe
• Created: 28/Aug/15 09:16
• Updated: 07/Mar/23 10:54

Description

(Broken out of CASSANDRA-10124)

We could add a USING INDEX <indexname> clause to SELECT syntax to force the choice of index and bypass the usual index selection mechanism.

CREATE TABLE ks.t1(k int, v1 int, v2 int, PRIMARY KEY (k));
CREATE INDEX v1_idx ON ks.t1(v1);
CREATE INDEX v2_idx ON ks.t1(v2);
CREATE CUSTOM INDEX v1_v2_idx ON ks.t1(v1, v2) USING 'com.foo.bar.CustomMultiColumnIndex';

# Override internal index selection mechanism
SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v1_idx;
SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v2_idx;
SELECT * FROM ks.t1 WHERE v1=0 AND v2=0 USING INDEX v1_v2_idx;

This is in some ways similar to index hinting in Oracle.

edit: fixed typo's (missing INDEX in the USING clauses)