DEV Community

Cover image for MySQL 8.0 vs 9.0: The Upgrade That Almost Broke Production
Igor Nosatov
Igor Nosatov

Posted on

MySQL 8.0 vs 9.0: The Upgrade That Almost Broke Production

#mysql #sql #programming

The "Just Upgrade" Myth

Here's what most comparison articles won't tell you: MySQL 9.0 isn't MySQL 8.1. It's a paradigm shift dressed as an incremental update.

The Breaking Changes Nobody Warns You About 

-- This worked fine in 8.0
SELECT * FROM users WHERE JSON_EXTRACT(metadata, '$.role') = 'admin';

-- In 9.0, suddenly...
-- Query execution time: 0.3s → 2.1s
Enter fullscreen mode Exit fullscreen mode

What happened? MySQL 9.0 completely rewrote the JSON optimizer. Some queries got faster. Mine got 7x slower.

The Five Game-Changers

1. Vector Search: Not Just Hype

Remember when everyone said "add pgvector to Postgres"? MySQL said "hold my beer."

-- MySQL 9.0: Native vector similarity search
CREATE TABLE embeddings (
    id INT PRIMARY KEY,
    content TEXT,
    vector VECTOR(1536) NOT NULL
);

-- Find similar documents
SELECT id, content, 
       VECTOR_DISTANCE(vector, :search_vector, 'cosine') AS similarity
FROM embeddings
ORDER BY similarity
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Real talk: This is 3-4x faster than storing vectors as JSON and using Python for similarity search. We moved our RAG pipeline from a separate vector DB to MySQL. One less service to maintain.

Before: App → MySQL → Python Service → Pinecone
After:  App → MySQL (done)
Enter fullscreen mode Exit fullscreen mode

2. JavaScript Stored Procedures (Yes, Really) 

-- MySQL 9.0 lets you write stored procedures in JavaScript
CREATE PROCEDURE calculate_discount(customer_id INT)
LANGUAGE JAVASCRIPT
AS $$
  const customer = db.query(
    'SELECT loyalty_points, tier FROM customers WHERE id = ?',
    [customer_id]
  );

  const discountRules = {
    gold: p => p > 1000 ? 0.20 : 0.15,
    silver: p => p > 500 ? 0.10 : 0.05,
    bronze: p => 0.02
  };

  return discountRules[customer.tier](customer.loyalty_points);
$$;
Enter fullscreen mode Exit fullscreen mode

Why this matters: No more context switching between SQL's clunky procedural syntax and your actual application code. Plus, you can unit test these functions outside the database.

3. Invisible Indexes: The Silent Assassin

MySQL 9.0 makes ALL new indexes invisible by default during creation to prevent query plan disruptions.

-- MySQL 8.0
CREATE INDEX idx_email ON users(email);
-- Immediately used by optimizer (might break existing query plans)

-- MySQL 9.0
CREATE INDEX idx_email ON users(email);
-- Index exists but optimizer ignores it

-- You must explicitly enable it
ALTER TABLE users ALTER INDEX idx_email VISIBLE;
Enter fullscreen mode Exit fullscreen mode

Production war story: We added an index to speed up one query. It made that query 10x faster but slowed down our main user lookup by 40% because the optimizer chose the wrong index. In 9.0, this can't happen accidentally.

4. Multi-Valued Indexes for JSON Arrays 

-- The problem in 8.0
-- You have: {"tags": ["javascript", "mysql", "performance"]}
-- You want: Fast lookup by ANY tag
-- You got: Full table scans 😢

-- MySQL 9.0 solution
CREATE TABLE articles (
    id INT PRIMARY KEY,
    metadata JSON,
    INDEX idx_tags ((CAST(metadata->'$.tags' AS CHAR(50) ARRAY)))
);

-- Now this is lightning fast
SELECT * FROM articles 
WHERE 'mysql' MEMBER OF (metadata->'$.tags');
Enter fullscreen mode Exit fullscreen mode

Benchmark:

  • 8.0: 450ms (full scan on 100k rows)
  • 9.0: 12ms (index seek)

5. Instant DDL for More Operations 

-- Operations that are now INSTANT in 9.0:
ALTER TABLE users 
  ADD COLUMN preferences JSON,
  DROP COLUMN legacy_field,
  RENAME COLUMN old_name TO new_name,
  MODIFY COLUMN status ENUM('active','inactive','suspended');

-- Zero downtime. No table copy. Magic.
Enter fullscreen mode Exit fullscreen mode

In 8.0, some of these would lock your table for minutes on large datasets. In 9.0, they're milliseconds.

The Migration Checklist (Learn from My Pain)

✅ Do This First 

# 1. Test in a staging environment (obvious, but I skipped it)
docker run --name mysql9-test -e MYSQL_ROOT_PASSWORD=test mysql:9.0

# 2. Run the upgrade checker
mysqlcheck --check-upgrade -u root -p

# 3. Review your slow query log for JSON operations
SELECT query_time, sql_text 
FROM mysql.slow_log 
WHERE sql_text LIKE '%JSON%'
ORDER BY query_time DESC;
Enter fullscreen mode Exit fullscreen mode

⚠️ Watch Out For

  1. Character set changes: Default is now utf8mb4_0900_ai_ci (affects sorting)
  2. Deprecated features removed: PROCEDURE ANALYSE() is gone
  3. Replication: 8.0 → 9.0 replication works, but 9.0 → 8.0 doesn't
  4. Query hints: Some optimizer hints behave differently

The Verdict: Should You Upgrade?

Upgrade if:

  • You're doing AI/ML work (vector search is worth it alone)
  • You have complex JSON queries
  • You need frequent schema changes
  • You want better developer experience (JavaScript procedures)

Stay on 8.0 if:

  • You have highly optimized 8.0 queries (test thoroughly first)
  • You're running legacy applications with minimal dev resources
  • Your MySQL workload is simple CRUD operations

The Performance Matrix

Feature MySQL 8.0 MySQL 9.0 My Use Case Winner
JSON Queries 🟡 Good 🟢 Excellent 9.0 (3x faster)
Vector Search ❌ N/A 🟢 Native 9.0 (obvious)
ALTER TABLE 🟡 Some instant 🟢 Most instant 9.0 (saved hours)
Stored Procedures 🔴 SQL only 🟢 JS + SQL 9.0 (maintainability)
Stability 🟢 Battle-tested 🟡 Newer 8.0 (for now)

My Actual Migration Path 

graph LR
    A[MySQL 8.0 Production] --> B[8.0 Staging Clone]
    B --> C[Upgrade to 9.0]
    C --> D[Run Test Suite]
    D --> E{All Tests Pass?}
    E -->|No| F[Fix Issues]
    F --> D
    E -->|Yes| G[Performance Testing]
    G --> H{Meets Benchmarks?}
    H -->|No| I[Query Optimization]
    I --> G
    H -->|Yes| J[Blue-Green Deploy]
    J --> K[MySQL 9.0 Production]
Enter fullscreen mode Exit fullscreen mode

Timeline: 3 weeks from decision to full production migration.

Code You Can Actually Use

Here's the script that saved me during migration:

-- Find queries that might perform differently in 9.0
WITH potential_issues AS (
  SELECT 
    DIGEST_TEXT,
    COUNT(*) as execution_count,
    AVG(TIMER_WAIT)/1000000000 as avg_time_ms
  FROM performance_schema.events_statements_summary_by_digest
  WHERE DIGEST_TEXT LIKE '%JSON%'
     OR DIGEST_TEXT LIKE '%ALTER TABLE%'
     OR DIGEST_TEXT LIKE '%CREATE INDEX%'
  GROUP BY DIGEST_TEXT
  HAVING execution_count > 100
)
SELECT * FROM potential_issues
ORDER BY avg_time_ms DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

The Bottom Line

MySQL 9.0 isn't a drop-in replacement. It's a strategic upgrade that requires planning. But if you're building modern applications—especially with AI, complex JSON, or frequent schema changes—it's absolutely worth the migration effort.

Would I upgrade again knowing what I know now? 100% yes.

Would I do it at 2 AM without testing? 100% no.

What's your MySQL story? Have you made the jump to 9.0? What broke? What got better? Drop your experiences in the comments—I'd love to hear what I missed.

Tags: #mysql #database #devops #performance

Cover Image Concept: Split screen showing a peaceful MySQL 8.0 server dashboard on the left, chaotic error logs on the right, with "The Upgrade" in bold text across the middle

Meta Description: "I upgraded from MySQL 8.0 to 9.0 in production. Here's what broke, what got 10x faster, and the migration checklist that will save you weeks of debugging."

Top comments (0)