mysql

name	mysql
description	MySQL patterns, schema design, indexing, query optimization with Prisma. Trigger: When designing MySQL schemas, optimizing queries, or configuring MySQL.
license	Apache-2.0
metadata	{"author":"gentleman-programming","version":"1.0"}

When to Use

• Designing MySQL table structures
• Creating or optimizing indexes
• Writing complex queries (raw SQL via Prisma)
• Configuring MySQL for Docker or production
• Performance tuning queries
• Choosing data types

---

Critical Patterns

Data Type Selection (with Prisma)

Use Case	MySQL Type	Prisma Type	Prisma Attribute
Short text (name, email)	VARCHAR(n)	String	@db.VarChar(255)
Long text (content, bio)	TEXT	String	@db.Text
Money/decimal	DECIMAL(10,2)	Decimal	@db.Decimal(10, 2)
Boolean	TINYINT(1)	Boolean	(auto)
Date/time	DATETIME	DateTime	@db.DateTime(0)
Positive integers	INT UNSIGNED	Int	@db.UnsignedInt
JSON data	JSON	Json	@db.Json
UUID/CUID	VARCHAR(30)	String	@id @default(cuid())
Enum	ENUM	Enum	Prisma enum type

Rules

• NEVER use TEXT for fields that need indexing — use VARCHAR
• ALWAYS specify lengths on VARCHAR — don't default to 255 for everything
• Use DATETIME(0) not TIMESTAMP — TIMESTAMP has Y2038 limit and timezone conversion
• Use DECIMAL for money — NEVER FLOAT or DOUBLE

---

Indexing Strategy

When to Add Indexes

model User {
  id        String @id @default(cuid())
  email     String @unique @db.VarChar(255)   // Unique = automatic index
  name      String @db.VarChar(100)
  role      Role   @default(USER)
  deletedAt DateTime? @db.DateTime(0)

  @@index([role])                              // Filtered queries on role
  @@index([deletedAt])                         // Soft delete queries
  @@index([role, deletedAt])                   // Composite for both
  @@map("users")
}

model Post {
  authorId  String
  published Boolean @default(false)
  createdAt DateTime @default(now()) @db.DateTime(0)

  @@index([authorId])                          // Foreign key
  @@index([published, createdAt(sort: Desc)])  // Published posts sorted by date
  @@map("posts")
}

Index Rules

Rule	Reason
Index ALL foreign keys	MySQL doesn't auto-index FKs
Composite index: most selective column first	Reduces scan range
Don't index low-cardinality alone (boolean)	Full scan may be faster
Composite index covers left-prefix queries	[a, b, c] covers WHERE a and WHERE a, b
Max 5-6 indexes per table	Write performance degrades

When NOT to Index

• Tables with < 1000 rows
• Columns with very low cardinality (boolean alone)
• Columns rarely used in WHERE/ORDER BY
• Write-heavy tables where reads are rare

---

Query Optimization

Use EXPLAIN (via Prisma raw)

const explain = await prisma.$queryRaw`EXPLAIN SELECT * FROM users WHERE email = '[e-mail]'`;

Common Performance Patterns

// ✅ Select only what you need
const users = await prisma.user.findMany({
  select: { id: true, name: true, email: true },
});

// ✅ Cursor pagination for large datasets
const posts = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastId },
  orderBy: { id: 'asc' },
});

// ❌ AVOID: Offset pagination on large tables
const posts = await prisma.post.findMany({
  skip: 10000,  // MySQL still scans 10000 rows
  take: 20,
});

// ✅ Count with where (uses index)
const count = await prisma.post.count({
  where: { published: true, authorId: userId },
});

// ❌ AVOID: N+1 queries
// BAD:
const users = await prisma.user.findMany();
for (const user of users) {
  const posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// ✅ GOOD: Include in single query
const users = await prisma.user.findMany({
  include: { posts: true },
});

---

MySQL Configuration (Docker)

# docker-compose.yml
services:
  mysql:
    image: mysql:8.4
    command: >
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
      --max-connections=200
      --innodb-buffer-pool-size=256M
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    volumes:
      - mysql_data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 10s
      timeout: 5s
      retries: 5

Connection String (Prisma)

DATABASE_URL="mysql://user:password@localhost:3306/dbname?connection_limit=10"

---

Decision Tree

Need full-text search?          → FULLTEXT index + Prisma raw query
Need JSON queries?              → @db.Json + path queries ($.field)
Large table pagination?         → Cursor-based (not offset)
Need unique + soft delete?      → Composite unique without deletedAt (app-level check)
Money calculations?             → DECIMAL(10,2) — never FLOAT
Need audit trail?               → createdAt + updatedAt + separate audit table

---

Common Queries (Prisma + MySQL)

// Full-text search (MySQL)
const results = await prisma.$queryRaw`
  SELECT * FROM posts
  WHERE MATCH(title, content) AGAINST(${searchTerm} IN NATURAL LANGUAGE MODE)
`;

// JSON field query
const products = await prisma.product.findMany({
  where: { metadata: { path: '$.color', equals: 'red' } },
});

// Group by with aggregation
const stats = await prisma.post.groupBy({
  by: ['authorId'],
  _count: { id: true },
  _avg: { views: true },
  orderBy: { _count: { id: 'desc' } },
  take: 10,
});

// Upsert
const user = await prisma.user.upsert({
  where: { email: '[e-mail]' },
  update: { name: 'Updated' },
  create: { email: '[e-mail]', name: 'New' },
});

---

Commands

# MySQL CLI (Docker)
docker exec -it mysql mysql -u root -p        # Root access
docker exec -it mysql mysql -u user -p dbname  # User access

# Useful MySQL commands
SHOW PROCESSLIST;                              # Active connections
SHOW INDEX FROM users;                         # Table indexes
EXPLAIN SELECT * FROM users WHERE email = 'x'; # Query plan
SHOW TABLE STATUS;                             # Table sizes
SELECT @@version;                              # MySQL version