# VNX-1089: Large Data Table with Excessive Number of Indices

A table defined in the project's SQL files that carries more than 8 secondary indices.

| Field | Value |
|---|---|
| Rule ID | `VNX-1089` |
| Severity | Low |
| SARIF level | `note` |
| Kind | `sast` |
| Languages | SQL |
| CWE | [CWE-1089](https://cwe.mitre.org/data/definitions/1089.html) |
| CAPEC | [CAPEC-66](https://capec.mitre.org/data/definitions/66.html) |
| ATT&CK | [T1190](https://attack.mitre.org/techniques/T1190/) |
| CWSS vector | `CWSS:1.0/TI:L/AP:RU/AL:S/IC:N/FC:LT/RP:RU/RL:S/AV:L/AS:N/IN:A/SC:A/BI:L/DI:H/EX:L/EC:N/P:L` |
| Tags | `excessive-indices` `database-performance` `cwe-1089` |
| Source | [`internal/sast/rules/vnx-1089.rego`](https://github.com/Vulnesis/cli/blob/main/internal/sast/rules/vnx-1089.rego) |

## Overview

VNX-1089 maps to [CWE-1089: Large Data Table with Excessive Number of Indices](https://cwe.mitre.org/data/definitions/1089.html). A table defined in the project's SQL files that carries more than 8 secondary indices.

## Why this matters

Every index is updated on every insert, update and delete. A heavily indexed table that grows large slows every write and can be pushed into exhausting I/O. CISQ, which CWE-1089 cites, suggests 3 indices for a table of a million rows.

## What gets flagged

One finding per table, at its first `CREATE TABLE` line (or its first index when the table is created elsewhere):

```sql
CREATE TABLE orders (            -- FLAGGED: 9 indices
    id BIGSERIAL PRIMARY KEY,
    ...
);
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status ON orders (status);
create index idx_orders_created on orders(created_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_orders_upd ON public.orders USING btree (updated_at);
-- ... five more
```

Counted: distinct index names from `CREATE [UNIQUE] INDEX` across every `.sql` file, so indices added in later migrations count, plus inline MySQL `KEY`/`INDEX`/`UNIQUE KEY`/`FULLTEXT KEY` definitions in the `CREATE TABLE` body.

## What does not get flagged

- Tables with 8 indices or fewer.
- A `CREATE INDEX IF NOT EXISTS` repeated in a later migration (counted once), and indices removed by `DROP INDEX`.
- The primary key, UNIQUE/CHECK constraints, and columns named `key` or `index`.
- Statements in `--` or `/* */` comments or inside string literals.

A table's row count cannot be known statically, so every table over the threshold is reported. The threshold of 8 is this rule's choice, set above the CISQ figure so the rule points at outliers.

## Remediation

1. Check which indices queries actually use (`pg_stat_user_indexes`, `sys.dm_db_index_usage_stats`, MySQL `sys.schema_unused_indexes`) and drop the unused ones.
2. Replace overlapping single-column indices with one composite index that serves the same queries.
3. For a deliberate exception, add `-- vulnetix-ignore: VNX-1089` on the reported line.

## References

- [CWE-1089: Large Data Table with Excessive Number of Indices](https://cwe.mitre.org/data/definitions/1089.html)

## Suppressing this rule

Put a `nosec` comment on the line, or on line 1 to cover the whole file. Use the comment marker of the file's language (`#`, `//`, `--` or `;`):

```text
# nosec vnx-1089
// nosec vnx-1089
```

Or record an ignore rule: `vulnetix ignore add --rule vnx-1089 --reason "..."`. See [Suppressing findings](/sast/guides/suppressing-findings.md).
