Cubic Index in SQL/CQL - Complete Guide

Overview

CubeCactus now includes Cubic Index integration for SQL and CQL queries, providing:

• Automatic Primary Indexing - All tables get cubic index on primary key
• Secondary Indexes - Create indexes on any column
• Query Optimization - Automatic index selection for faster queries
• Multi-Level Storage - Cubic progression (1³×6, 2³×6, 3³×6...) for optimal distribution

Features

✅ CREATE INDEX - Create secondary indexes on columns
✅ DROP INDEX - Remove indexes
✅ SHOW INDEXES - View all indexes on a table
✅ Automatic Optimization - Queries automatically use indexes when available
✅ Index Statistics - Monitor index performance
✅ Multi-Level Cubic Distribution - Efficient key distribution across levels

SQL Syntax

CREATE INDEX

-- Basic syntax
CREATE INDEX index_name ON table(column);

-- Examples
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_category ON products(category);

-- With keyspace
CREATE INDEX idx_status ON myapp.orders(status);

DROP INDEX

-- Drop an index
DROP INDEX idx_email;
DROP INDEX idx_age;

SHOW INDEXES

-- Show all indexes on a table
SHOW INDEXES ON users;
SHOW INDEXES ON myapp.orders;

Complete Examples

Example 1: E-Commerce Product Catalog

-- Create products table
CREATE TABLE shop.products (
    sku TEXT PRIMARY KEY,
    name TEXT,
    category TEXT,
    price TEXT,
    stock TEXT
);

-- Insert products
INSERT INTO shop.products (sku, name, category, price, stock)
VALUES ('LAPTOP-001', 'MacBook Pro', 'Electronics', '2499.99', '10');

INSERT INTO shop.products (sku, name, category, price, stock)
VALUES ('MOUSE-001', 'Wireless Mouse', 'Accessories', '29.99', '50');

INSERT INTO shop.products (sku, name, category, price, stock)
VALUES ('KEYBOARD-001', 'Mechanical Keyboard', 'Accessories', '149.99', '25');

-- Create index on category for fast filtering
CREATE INDEX idx_category ON shop.products(category);

-- Query by SKU (uses primary index automatically)
SELECT * FROM shop.products WHERE sku = 'LAPTOP-001';
-- Response shows: "indexUsed": "PRIMARY", "cubicLevel": 2

-- Query by category (uses secondary index)
SELECT * FROM shop.products WHERE category = 'Accessories';
-- Response shows: "indexUsed": "SECONDARY"

-- Show all indexes
SHOW INDEXES ON shop.products;
-- Response:
-- {
--   "indexes": [
--     {"name": "PRIMARY", "column": "sku", "type": "PRIMARY", "keys": 3},
--     {"name": "idx_category", "column": "category", "type": "SECONDARY", "keys": 3}
--   ]
-- }

Example 2: User Management System

-- Create users table
CREATE TABLE app.users (
    user_id TEXT PRIMARY KEY,
    username TEXT,
    email TEXT,
    status TEXT,
    created_at TEXT
);

-- Insert users
INSERT INTO app.users (user_id, username, email, status, created_at)
VALUES ('user-001', 'alice', 'alice@example.com', 'active', '2026-01-01');

INSERT INTO app.users (user_id, username, email, status, created_at)
VALUES ('user-002', 'bob', 'bob@example.com', 'active', '2026-01-02');

INSERT INTO app.users (user_id, username, email, status, created_at)
VALUES ('user-003', 'charlie', 'charlie@example.com', 'inactive', '2026-01-03');

-- Create indexes for common queries
CREATE INDEX idx_email ON app.users(email);
CREATE INDEX idx_status ON app.users(status);
CREATE INDEX idx_username ON app.users(username);

-- Query by email (fast secondary index lookup)
SELECT * FROM app.users WHERE email = 'alice@example.com';

-- Query by status (filtered via index)
SELECT * FROM app.users WHERE status = 'active';

-- View index statistics
-- (Via API: GET /api/v1/index/stats)

Example 3: Order Processing

-- Create orders table
CREATE TABLE sales.orders (
    order_id TEXT PRIMARY KEY,
    customer_id TEXT,
    status TEXT,
    total TEXT,
    created_at TEXT
);

-- Insert orders
INSERT INTO sales.orders (order_id, customer_id, status, total, created_at)
VALUES ('ord-001', 'cust-001', 'pending', '299.99', '2026-02-15');

INSERT INTO sales.orders (order_id, customer_id, status, total, created_at)
VALUES ('ord-002', 'cust-001', 'shipped', '149.99', '2026-02-14');

INSERT INTO sales.orders (order_id, customer_id, status, total, created_at)
VALUES ('ord-003', 'cust-002', 'delivered', '499.99', '2026-02-13');

-- Index for customer queries
CREATE INDEX idx_customer ON sales.orders(customer_id);

-- Index for status filtering
CREATE INDEX idx_order_status ON sales.orders(status);

-- Find all orders for a customer
SELECT * FROM sales.orders WHERE customer_id = 'cust-001';
-- Uses idx_customer secondary index

-- Find pending orders
SELECT * FROM sales.orders WHERE status = 'pending';
-- Uses idx_order_status secondary index

How It Works

Cubic Index Levels

The Cubic Index uses a multi-level tree structure:

Level 1: Index Value = 1³ × 6 = 6      (capacity for 6 keys)
Level 2: Index Value = 2³ × 6 = 48     (capacity for 48 keys)
Level 3: Index Value = 3³ × 6 = 162    (capacity for 162 keys)
Level 4: Index Value = 4³ × 6 = 384    (capacity for 384 keys)
Level 5: Index Value = 5³ × 6 = 750    (capacity for 750 keys)

Keys are distributed across levels based on hash value, ensuring balanced distribution.

Primary Index (Automatic)

Every table automatically gets a Primary Index on the primary key:

CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT);
-- Automatically creates cubic index: users -> id

Index Structure:

Primary Key -> Serialized Row Data

Secondary Index (Manual)

Create indexes on non-primary-key columns:

CREATE INDEX idx_email ON users(email);

Index Structure:

Column Value -> Primary Key -> Row Data

Query Flow:

1. Look up column value in secondary index → get primary key
2. Look up primary key in primary index → get row data

Performance Benefits

Without Index

Query: SELECT * FROM users WHERE email = 'alice@example.com'
Method: Full table scan
Time: O(n) - must check every row

With Index

Query: SELECT * FROM users WHERE email = 'alice@example.com'
Method: Cubic index lookup
Time: O(1) - direct hash-based lookup
Levels traversed: Typically 1-3 levels

Benchmark Results

Note: Indexes add slight overhead to writes but dramatically speed up reads.

Index Statistics

Via SQL

SHOW INDEXES ON users;

Via API

curl http://localhost:8080/api/v1/index/stats

Response Example

{
  "indexHits": 1523,
  "indexMisses": 47,
  "hitRate": "97.01%",
  "queriesOptimized": 1523,
  "primaryIndexes": 5,
  "secondaryIndexes": 12,
  "totalIndexes": 17,
  "primaryIndexDetails": {
    "shop.products": {
      "totalLevels": 3,
      "totalKeys": 150,
      "levels": {
        "Level-1": {"keys": 6, "capacity": 6, "utilization": "100.00%"},
        "Level-2": {"keys": 48, "capacity": 48, "utilization": "100.00%"},
        "Level-3": {"keys": 96, "capacity": 162, "utilization": "59.26%"}
      }
    }
  },
  "secondaryIndexDetails": {
    "idx_category": {
      "indexKey": "shop.products.category",
      "totalKeys": 3,
      "levels": 1
    }
  }
}

Best Practices

1. Index Frequently Queried Columns

-- Good: Index columns used in WHERE clauses
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_email ON users(email);

-- Avoid: Indexing rarely queried columns
-- CREATE INDEX idx_middle_name ON users(middle_name); -- Don't do this

2. Limit Number of Indexes

• Rule of Thumb: 2-5 secondary indexes per table
• Reason: Each index adds write overhead
• Consider: Only index columns queried frequently

3. Monitor Index Usage

-- Regularly check index stats
SHOW INDEXES ON users;

-- Drop unused indexes
DROP INDEX idx_rarely_used;

4. Primary Key Design

-- Good: Use meaningful, stable primary keys
CREATE TABLE orders (order_id TEXT PRIMARY KEY, ...);

-- Avoid: Auto-incrementing integers (poor distribution)
-- CREATE TABLE orders (id INT PRIMARY KEY, ...); -- Not ideal for cubic index

5. Update vs Query Balance

• Read-heavy workload: Use many indexes
• Write-heavy workload: Use fewer indexes
• Balanced workload: 2-3 strategic indexes

Troubleshooting

Index Not Being Used

Problem:

SELECT * FROM users WHERE email = 'alice@example.com';
-- Not showing "indexUsed": "SECONDARY"

Solution:

-- Check if index exists
SHOW INDEXES ON users;

-- Create index if missing
CREATE INDEX idx_email ON users(email);

Slow Index Creation

Problem:

CREATE INDEX idx_category ON products(category);
-- Takes a long time on large tables

Reason: Index must scan existing data

Solution:

• Create indexes before inserting bulk data
• Or accept one-time cost

High Memory Usage

Problem: Too many indexes consuming memory

Solution:

-- Drop unused indexes
DROP INDEX idx_rarely_used;

-- Monitor index stats
curl http://localhost:8080/api/v1/index/stats

Advanced Features

Cubic Level Distribution

View which cubic level your keys are stored in:

SELECT * FROM products WHERE sku = 'LAPTOP-001';
-- Response includes: "cubicLevel": 2

Index Rebalancing

Rebalance indexes via API:

curl -X POST http://localhost:8080/api/v1/index/rebalance

Clear All Indexes

curl -X POST http://localhost:8080/api/v1/index/clear

API Integration

REST Endpoints

# Execute indexed SQL
curl -X POST http://localhost:8080/api/v1/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE INDEX idx_email ON users(email)"}'

# Get index statistics
curl http://localhost:8080/api/v1/index/stats

# Rebalance indexes
curl -X POST http://localhost:8080/api/v1/index/rebalance

# Clear indexes
curl -X POST http://localhost:8080/api/v1/index/clear

Summary

✅ Automatic Primary Indexing - Every table gets cubic index
✅ Secondary Indexes - CREATE INDEX on any column
✅ Query Optimization - Automatic index selection
✅ Multi-Level Storage - Cubic progression for efficiency
✅ Performance Monitoring - Detailed index statistics
✅ Production Ready - Battle-tested implementation

Start using Cubic Indexes today for faster queries! 🌵⚡

-- Create table
CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT, email TEXT);

-- Create index
CREATE INDEX idx_email ON users(email);

-- Query with index
SELECT * FROM users WHERE email = 'alice@example.com';
-- Automatic cubic index optimization! 🚀