Can my DB?

-- Covering index: customer_id is the key, order_date and total are included
CREATE INDEX idx_orders_covering
  ON orders (customer_id)
  INCLUDE (order_date, total);

-- This query can be answered from the index alone (index-only scan)
SELECT order_date, total
FROM   orders
WHERE  customer_id = 42;

-- Unique index with INCLUDE columns
CREATE UNIQUE INDEX idx_users_email_covering
  ON users (email)
  INCLUDE (id, display_name);

-- Verify with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
  SELECT order_date, total FROM orders WHERE customer_id = 42;
-- Look for 'Index Only Scan' in the output

• INCLUDE columns can be any type, including non-orderable types that would be illegal as key columns.
• Index-only scans still consult the visibility map — recently modified pages may force a heap fetch even when the index covers the query.

-- MySQL has no INCLUDE syntax, but InnoDB secondary indexes
-- implicitly include the primary key column(s).

-- Primary key
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  total       DECIMAL(10,2),
  INDEX idx_customer (customer_id)  -- implicitly: (customer_id, id)
);

-- This query can use a covering index (customer_id + id from PK):
SELECT id FROM orders WHERE customer_id = 42;

-- For a true multi-column covering index, include all needed columns:
CREATE INDEX idx_cust_date_total ON orders (customer_id, order_date, total);

-- Now this can be answered from the index alone:
SELECT order_date, total FROM orders WHERE customer_id = 42;

-- EXPLAIN to check: look for 'Using index' in Extra column
EXPLAIN SELECT order_date, total FROM orders WHERE customer_id = 42;

• InnoDB secondary indexes implicitly append the primary key columns at the leaf, so they already cover queries that need just the indexed columns plus the PK.
• For broader coverage, add every needed column to the index key. EXPLAIN shows 'Using index' when the query is index-only.

-- Same InnoDB implicit PK inclusion as MySQL.
-- Create a multi-column index to cover the query:
CREATE INDEX idx_cust_date_total ON orders (customer_id, order_date, total);

-- Covered query (no heap access needed):
SELECT order_date, total FROM orders WHERE customer_id = 42;

-- EXPLAIN shows 'Using index' for a covered query:
EXPLAIN SELECT order_date, total FROM orders WHERE customer_id = 42;

• Same InnoDB story as MySQL; Aria has similar coverage behavior.

-- Oracle has no INCLUDE syntax.
-- Workaround: add extra columns as trailing non-searched key columns.
-- (They participate in ordering but are seldom filtered on.)
CREATE INDEX idx_orders_covering
  ON orders (customer_id, order_date, total);

-- This can be answered by an index fast full scan:
SELECT order_date, total FROM orders WHERE customer_id = 42;

-- Index-organized tables (IOT) store all columns in the index:
CREATE TABLE orders_iot (
  id          NUMBER PRIMARY KEY,
  customer_id NUMBER,
  order_date  DATE,
  total       NUMBER(12,2)
) ORGANIZATION INDEX
  OVERFLOW TABLESPACE users;

• No INCLUDE. Append columns to the key as trailing columns to make the index cover the query.
• Index-Organized Tables store the full row in the B-tree, making every query on an IOT effectively index-only.
• COMPRESS reduces index size when trailing columns are repetitive.

-- Covering index with INCLUDE columns
CREATE INDEX idx_orders_covering
  ON orders (customer_id)
  INCLUDE (order_date, total);

-- This query is answered from the index alone:
SELECT order_date, total
FROM   orders
WHERE  customer_id = 42;

-- Unique index with INCLUDE
CREATE UNIQUE INDEX idx_users_email
  ON users (email)
  INCLUDE (id, display_name);

-- Check for covering index usage in execution plan:
-- Look for 'Index Seek' with no 'Key Lookup' node

-- View index columns
SELECT i.name, ic.key_ordinal, ic.is_included_column, c.name AS col
FROM   sys.indexes i
JOIN   sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN   sys.columns c        ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE  i.object_id = OBJECT_ID('orders');

• Added in 2005. Up to 1023 INCLUDE columns per index.
• A covered query avoids the Key Lookup operator — watch for it (or its absence) in the execution plan.
• sys.index_columns.is_included_column = 1 identifies INCLUDE columns.

-- SQLite has no INCLUDE syntax.
-- Include all needed columns as key columns:
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date, total);

-- This query can be answered from the index alone:
SELECT order_date, total FROM orders WHERE customer_id = 42;

-- Verify with EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN
  SELECT order_date, total FROM orders WHERE customer_id = 42;
-- Output shows 'COVERING INDEX idx_orders_covering' if covered

• No INCLUDE. List every needed column in the key. EXPLAIN QUERY PLAN labels the result COVERING INDEX.

-- Redshift has no user-managed indexes.
-- Columnar storage means any query automatically reads only the needed columns.
-- Use sort keys and distribution keys for performance tuning:
CREATE TABLE orders (
  id          BIGINT,
  customer_id INT,
  order_date  DATE,
  total       DECIMAL(10,2)
)
DISTKEY (customer_id)
SORTKEY (customer_id, order_date);

• No indexes. Columnar storage already reads only the referenced columns; SORTKEY drives block pruning and DISTKEY drives data placement.

-- Db2 supports INCLUDE columns on unique indexes.
CREATE UNIQUE INDEX idx_orders_covering
  ON orders (customer_id)
  INCLUDE (order_date, total);

-- This query can be answered from the index alone:
SELECT order_date, total
FROM   orders
WHERE  customer_id = 42;

-- View index columns
SELECT indname, colnames
FROM   syscat.indexes
WHERE  tabname = 'ORDERS';

• INCLUDE is only available on unique indexes — narrower than PostgreSQL or SQL Server.

-- Snowflake has no user-managed indexes.
-- Columnar storage automatically reads only needed columns.
-- Use cluster keys to co-locate related rows:
ALTER TABLE orders CLUSTER BY (customer_id);

-- Search Optimization for selective lookups:
ALTER TABLE orders ADD SEARCH OPTIMIZATION ON EQUALITY(customer_id);

• No indexes. Micro-partitions already serve as column-covering storage; CLUSTER BY and Search Optimization handle selective-lookup performance.

-- BigQuery has no user-managed indexes.
-- Columnar storage automatically reads only needed columns.
-- Use clustered and partitioned tables for query optimization:
CREATE TABLE orders
PARTITION BY DATE(order_date)
CLUSTER BY customer_id
AS SELECT * FROM staging.orders;

• No indexes. Columnar storage plus PARTITION BY and CLUSTER BY is the full toolkit; reducing scanned bytes directly reduces cost.