Name: mysql
Rating: 92
Author: cosechasistema

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

prisma

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)

typescript

const explain = await prisma.$queryRaw`EXPLAIN SELECT * FROM users WHERE email = 'test@test.com'`;

Common Performance Patterns

typescript

// ✅ 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)

yaml

# 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)

code

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

Decision Tree

code

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)

typescript

// 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: 'test@test.com' },
  update: { name: 'Updated' },
  create: { email: 'test@test.com', name: 'New' },
});

Commands

bash

# 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