SkillAgentSearch skills...

nw-query-optimization

SQL and NoSQL query optimization techniques, indexing strategies, execution plan analysis, JOIN algorithms, cardinality estimation, and database-specific query patterns

Install / Use

npx skills add nWave-ai/nWave --skill nw-query-optimization

Installs into whichever agent you are using.

About this skill
📄

SKILL.md

Installable skill definition

Quality Score

87/100

Supported Platforms

Universal

Our assessment of nw-query-optimization

nw-query-optimization scores 87/100 on our quality scale, 401st of 608 Data & Analytics skills we index.

Its SKILL.md is 5.2 KB long, well organised into 21 sections with 4 code examples: a solid amount of guidance for an agent.

It has 617 GitHub stars, a meaningful sign that others use it.

Substance
26/30
Structure
20/20
Description
15/15
Adoption
12/20
Freshness
15/15

Maintenance, license and trust

  • The repository was last updated about 2 months ago, so nw-query-optimization is actively maintained.
  • Our last check on 2026-10-11 found the source still online.
  • It is released under the MIT license, a permissive license that allows use, modification and commercial use with attribution.
  • Its trust signals score 100/100, with no cautions. These come from repository metadata, not a code audit — read the skill file before letting an agent act on it.

Safety scan

No issues found

Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful.

AI review by kimi-k2.7-code on 2026-10-04. Automated pattern scan on 2026-10-04. It catches known dangerous patterns, not every risk — read a skill before letting an agent act on it.

nw-query-optimization compared with similar skills

All 4 of these similar skills score higher than nw-query-optimization; compare them before choosing.

SkillScoreStarsUpdatedFormat
nw-query-optimization (this skill)by nWave-ai876172mo agoSKILL.md
claude-memby thedotmack100117.9ktodayCLAUDE.md
x402by browser-use100121.0k4d agoSKILL.md
algorithmic-artby anthropics100177.9k56d agoSKILL.md
pptxby anthropics100177.9k56d agoSKILL.md

Frequently asked questions

How do I install nw-query-optimization?
Run npx skills add nWave-ai/nWave --skill nw-query-optimization. The install tabs above show the steps for each supported agent.
Which AI agents does nw-query-optimization work with?
It is written for Universal, as a SKILL.md file. Other agents that read the same format can often use it too.
Is nw-query-optimization safe to use?
Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful. It is MIT-licensed and scores 100/100 on trust signals. Skills are instructions an agent will follow, so read the file before installing it and do not approve commands you do not understand.
Is nw-query-optimization still maintained?
The repository was last updated about 2 months ago, so nw-query-optimization is actively maintained.

name: nw-query-optimization description: SQL and NoSQL query optimization techniques, indexing strategies, execution plan analysis, JOIN algorithms, cardinality estimation, and database-specific query patterns user-invocable: false disable-model-invocation: true

Query Optimization

Cost-Based Optimization

Modern relational DBs use cost-based optimizers (CBO): generate plan candidates -> estimate cost via statistics (row counts, distributions, selectivity) -> select lowest I/O/CPU/memory plan. Stale statistics lead to suboptimal plans.

Execution Plan Analysis

Validate optimization with EXPLAIN before and after changes.

-- PostgreSQL (add ANALYZE for actual runtime stats)
EXPLAIN ANALYZE SELECT order_id, total FROM orders WHERE customer_id = 12345;
-- MySQL: EXPLAIN FORMAT=JSON ... | SQL Server: SET STATISTICS IO ON

Key indicators: Seq Scan/Table Scan = missing index | Index Scan/Seek = efficient | Hash Join = large equality joins | Nested Loop = small/indexed inner | Merge Join = pre-sorted inputs | Sort = watch disk spills

Indexing Strategies

B-Tree (Default)

Supports: equality, range, sorting, prefix matching | O(log n) lookup | General-purpose, all major DBs default

Hash

Equality only | O(1) lookup | High-cardinality exact-match | No range/sorting/pattern support

Covering Indexes

Include all query columns in index -> eliminates table access (index-only scan) | Trade-off: larger index, slower writes

-- Covering index for: SELECT name, email FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering ON users(status) INCLUDE (name, email);

PostgreSQL Specialized

  • GiST: Geometric data, full-text search, nearest-neighbor
  • GIN: Arrays, full-text search, JSONB queries
  • BRIN: Large tables with physically correlated data (timestamps), minimal storage
  • SP-GiST: Non-balanced structures, point-based geometric queries

Compound Index Design

Order by: 1. Equality conditions first (highest selectivity) | 2. Sort columns second | 3. Range conditions last

MongoDB ESR Rule

Equality-Sort-Range ordering for compound indexes:

// Query: status = "A", qty > 20, sorted by item
// Optimal index:
db.collection.createIndex({ status: 1, item: 1, qty: 1 })
//                          E(quality)  S(ort)   R(ange)

SQL Optimization Patterns

Select Only Needed Columns

-- Bad: SELECT * retrieves unnecessary data, prevents covering indexes
SELECT * FROM orders WHERE customer_id = 12345;

-- Good: Specify columns, enables covering index
SELECT order_id, order_date, total FROM orders WHERE customer_id = 12345;

Other Key Patterns

  • CTEs: Improve readability but not always performance -- PostgreSQL may materialize CTEs (pre-v12), MySQL inlines them
  • Window functions: Use SUM() OVER, RANK() OVER (PARTITION BY ...) for analytics without self-joins
  • Pagination: Prefer keyset (WHERE id > last_seen ORDER BY id LIMIT N) over OFFSET for deep pages
  • Parameterized queries: Prevent SQL injection AND enable plan caching (cursor.execute("... WHERE id = %s", (id,)))

JOIN Algorithm Selection

| Algorithm | Best When | Cost | |-----------|-----------|------| | Nested Loop | Small outer table, indexed inner table | O(n * m) worst, O(n * log m) with index | | Hash Join | Large tables, equality joins, no useful indexes | O(n + m) build + probe | | Merge Join | Both inputs already sorted (index order) | O(n + m) after sort |

Cardinality Estimation

Optimizer predicts row counts using: Histograms (value distribution) | Density vectors (non-histogram columns) | Statistics objects via ANALYZE (PostgreSQL) / UPDATE STATISTICS (SQL Server)

When estimation is wrong (correlated columns, skewed data, multi-table joins): 1. Run ANALYZE/UPDATE STATISTICS | 2. Create multi-column statistics | 3. Query hints as last resort

NoSQL Query Optimization

MongoDB

Place $match/$project early in pipelines | Use $lookup sparingly (left outer joins) | Compound indexes following ESR | Validate with explain("executionStats")

Cassandra

Always include partition key | Design tables around query patterns (query-first) | Use SAI over SASI (43% throughput gain) | Avoid ALLOW FILTERING (full cluster scan) | Materialized views add write overhead

DynamoDB

Use Query not Scan | Design partition keys for even distribution | GSIs for alternative access patterns | Single-table design with composite sort keys

Redis

FT.SEARCH for complex queries (RediSearch module) | Design key naming for efficient SCAN | Use pipelining for batch ops

Anti-Patterns to Detect

  • **SELECT ***: Wastes I/O, prevents covering indexes
  • Missing indexes on WHERE/JOIN/ORDER BY columns: full table scans
  • N+1 queries: Fetch in loops instead of JOINs/batch
  • Implicit type conversions: Prevents index use (WHERE varchar_col = 123)
  • Functions on indexed columns: WHERE UPPER(name) = 'JOHN' blocks index; use function-based indexes
  • Missing pagination: Unbounded result sets
  • Hot partitions (NoSQL): Low-cardinality partition keys concentrate load
  • ALLOW FILTERING (Cassandra): Expensive full-cluster scans
  • Large partitions (Cassandra): >100MB degrades performance

Related Skills

View on GitHub
GitHub Stars617
CategoryData
Updated2mo ago
Forks63

Languages

Python

Trust signals

100/100

From repository metadata: license, adoption, age and documentation. Not a code audit — see the Safety scan above for what the skill file itself contains.

No cautions