Help us improve
Share bugs, ideas, or general feedback.
From acc
Detects missing and suboptimal database indexes in PHP code. Flags unindexed WHERE/JOIN columns, incorrect composite order, covering opportunities, and index-defeating patterns like functions or leading LIKE wildcards.
npx claudepluginhub dykyi-roman/awesome-claude-code --plugin accHow this skill is triggered — by the user, by Claude, or both
Slash command
/acc:check-index-usageThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Analyze PHP code for missing or suboptimal database index usage.
Analyzes PHP code for SQL query efficiency issues including SELECT *, missing index hints, unnecessary joins, full table scans, suboptimal WHERE clauses, ORDER BY without indexes, and large OFFSET pagination.
Reviews SQL queries for performance anti-patterns, N+1 issues, missing indexes, and unsafe operations. Analyzes raw SQL, ORM-generated queries, and migrations with optimization recommendations.
Analyzes PostgreSQL and MySQL index usage to detect missing indexes causing sequential scans, unused indexes, and recommend optimal configurations for query performance.
Share bugs, ideas, or general feedback.
Analyze PHP code for missing or suboptimal database index usage.
// CRITICAL: Filtering on unindexed column
$qb->select('o')
->from(Order::class, 'o')
->where('o.status = :status') // Is 'status' indexed?
->andWhere('o.createdAt > :date') // Is 'createdAt' indexed?
->setParameter('status', 'pending')
->setParameter('date', $date);
// Detection: Extract columns from WHERE, check entity for indexes
// CRITICAL: JOIN on non-indexed foreign key
$qb->select('o', 'i')
->from(Order::class, 'o')
->join('o.items', 'i') // Is foreign key indexed?
->where('i.productId = :productId'); // Is productId indexed?
// Doctrine annotation check:
// @ORM\ManyToOne without @ORM\Index on the join column
// Entity mapping:
#[ORM\Index(columns: ['created_at', 'status'])] // Index order
// Query:
->where('o.status = :status') // Equality first
->andWhere('o.createdAt > :date') // Range second
// WRONG ORDER! Should be Index(columns: ['status', 'created_at'])
// Equality columns first, then range columns
// CRITICAL: Function prevents index usage
$qb->where('YEAR(o.createdAt) = :year'); // Index on createdAt NOT used!
$qb->where('LOWER(u.email) = :email'); // Index on email NOT used!
$qb->where('LENGTH(u.name) > :len'); // Index on name NOT used!
// CORRECT: Rewrite to use index
$qb->where('o.createdAt >= :yearStart AND o.createdAt < :yearEnd');
$qb->where('u.email = :email'); // Store normalized, query normalized
// CRITICAL: Leading wildcard prevents index usage
$qb->where("u.name LIKE :name")
->setParameter('name', "%{$search}%"); // Full table scan!
// Partially indexed:
$qb->where("u.name LIKE :name")
->setParameter('name', "{$search}%"); // Can use index (prefix match)
// For full-text search, use dedicated solution:
// Full-text index, Elasticsearch, or application-level search
// VULNERABLE: OR can prevent index usage
$qb->where('o.status = :s1 OR o.priority = :p1');
// Unless BOTH status AND priority are indexed, this may full-scan
// CORRECT: Use UNION or separate queries for complex OR
$qb->where('o.status = :s1')
->orWhere('o.priority = :p1');
// Consider: separate queries + merge results if performance critical
// Doctrine entity without index on FK
#[ORM\Entity]
class OrderItem
{
#[ORM\ManyToOne(targetEntity: Order::class)]
#[ORM\JoinColumn(name: 'order_id')]
private Order $order;
// 'order_id' column may not be indexed!
// MySQL InnoDB auto-indexes FKs, but PostgreSQL does NOT
}
// SLOW: Sorting large result set without index
$qb->select('o')
->from(Order::class, 'o')
->where('o.userId = :userId')
->orderBy('o.createdAt', 'DESC') // Is (userId, createdAt) composite index?
->setMaxResults(20);
// Without composite index: fetch ALL user orders, sort in memory, return 20
// With composite index: read 20 rows directly from index
# WHERE conditions (columns to check for indexes)
Grep: "->where\(|->andWhere\(|->orWhere\(" --glob "**/*Repository*.php"
Grep: "WHERE.*=|WHERE.*LIKE|WHERE.*IN" --glob "**/*.php"
# JOIN columns
Grep: "->join\(|->leftJoin\(|->innerJoin\(" --glob "**/*Repository*.php"
Grep: "JOIN.*ON" --glob "**/*.php"
# Functions on columns (index defeat)
Grep: "YEAR\(|MONTH\(|DATE\(|LOWER\(|UPPER\(|LENGTH\(" --glob "**/*.php"
# LIKE with variable
Grep: "LIKE.*%.*\\\$|LIKE.*:.*\n.*%\\\$" --glob "**/*.php"
# ORDER BY
Grep: "->orderBy\(|ORDER BY" --glob "**/*Repository*.php"
# Entity index annotations
Grep: "#\[ORM\\\\Index|@ORM\\\\Index|@Index" --glob "**/Domain/**/*.php"
# Foreign keys
Grep: "ManyToOne|OneToMany|ManyToMany|JoinColumn" --glob "**/Domain/**/*.php"
| Pattern | Severity |
|---|---|
| WHERE on unindexed column (large table) | 🔴 Critical |
| JOIN without FK index (PostgreSQL) | 🔴 Critical |
| Function on indexed column | 🟠 Major |
| Leading wildcard LIKE | 🟠 Major |
| Wrong composite index order | 🟠 Major |
| ORDER BY without covering index | 🟡 Minor |
| Missing index on small table | 🟡 Minor |
### Index Usage: [Description]
**Severity:** 🔴/🟠/🟡
**Location:** `file.php:line`
**Table/Entity:** [Table name]
**Column(s):** [Column names]
**Issue:**
[Description — missing index, wrong order, function defeating index]
**Query Pattern:**
```php
// The query that needs index support
Recommended Index:
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
Expected Improvement: Full table scan → index seek (1000x faster on large tables)