padosoft-database-designlisted

Use this skill when writing or reviewing a migration or a table design — adding a table or a column, choosing indexes, deciding a primary key, partitioning a large table, adding a JSON column, planning for growth. Also when the user reports a slow query blamed on a missing index, a migration that fails because a table or column already exists (or does not), an index that is never used, a table too big to purge, or asks how to index a query they are about to write. It covers index selection and redundancy, the composite-prefix rule, covered indexes, partitioning constraints, JSON columns, and the guard clauses a migration needs to be re-runnable. Do not use it for writing the queries themselves (padosoft-query-performance), for ORM conventions, or for choosing a database engine.
padosoft/skills · ★ 1 · Data & Documents · score 80

Install: claude install-skill padosoft/skills

# Database design An index is not a performance tweak added later — it is part of the table's design, and so is the primary key, the partition and the decision to put something in a JSON column. Getting them wrong is cheap to fix on day one and expensive on day four hundred. --- ## 1. Which columns get an index by default Always, unless there is a reason not to: **boolean and flag columns, dates and datetimes, and descriptive text columns** (name, title, description) — because those are what the admin side filters, sorts and searches on, systematically. Then, without exception: **every column involved in a `JOIN`, `WHERE`, `ORDER BY`, `GROUP BY` or `HAVING`**. Two exemptions: the column is already the **leftmost prefix** of an existing composite index, or the table will never hold more than a few thousand rows. ```php $table->index('active'); $table->index('starts_at'); $table->index(['brand_id', 'active'], 'idx_art_brand_active'); // idx_<table>_<col>_<col> ``` ## 2. Designing the index for a query: three stars | Star | Columns | Not available when | |---|---|---| | ★ | the equality predicates of the `WHERE`, joined by `AND` | the `WHERE` uses `OR` or complex expressions | | ★★ | the `ORDER BY` / `GROUP BY` columns, **in their direction** | grouping and ordering differ | | ★★★ | the remaining selected columns — a covered index | optional, but it removes the row lookup entirely | ```sql -- SELECT name, price FROM articles WHERE brand_id = ? AND active = 1 ORDER BY