Replacing Heavy MySQL Table JOINs with Elasticsearch: Boosting PageSpeed Score from 29 to 69

Introduction

In relational database systems like MySQL, querying article data often involves joining multiple tables: articles, categories, article_tags, tags, and authors.

While relational schema design is excellent for transactional integrity (ACID), running complex multi-table SQL JOINs—especially combined with wildcard text filtering (LIKE '%keyword%')—becomes a severe bottleneck as dataset sizes and concurrent traffic expand.

In this article, I share why and how we replaced heavy MySQL JOIN queries with Elasticsearch 8 document indexing, boosting our Google PageSpeed Performance score from 29 to 69 while significantly reducing database load.

1. The Problem: The Cost of Relational JOINs at Scale

When an end user or mobile application requests a list of articles or searches for a specific topic, a typical relational SQL query looks like this:

-- Generalized SQL JOIN query example across multiple tables
SELECT a.id, a.title, a.slug, c.name AS category_name, GROUP_CONCAT(t.name) AS tag_names
FROM articles a
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
WHERE a.status = 'published' AND (a.title LIKE '%skincare%' OR a.content LIKE '%skincare%')
GROUP BY a.id
ORDER BY a.published_at DESC
LIMIT 20;

(Note: The table schema and SQL query above have been generalized for illustration purposes).

Why this query degrades under traffic:

1. Full Table Scans: LIKE '%term%' cannot utilize standard B-Tree indices efficiently, forcing MySQL to scan raw text blocks across disk.
2. Temporary Tables & Sorting: GROUP BY and ORDER BY force MySQL to create temporary memory/disk tables to aggregate JOIN results.
3. Connection Exhaustion: Under concurrent requests, CPU usage spikes on read replicas, exhausting connection pools and causing page load timeouts.

2. The Solution: Denormalized Document Indexing

Instead of forcing the database to perform expensive JOINs dynamically on every incoming HTTP request, we denormalized article data into a single, flat Elasticsearch JSON document.

{
  "id": 10452,
  "title": "Top 10 Skincare Essentials for Summer",
  "slug": "top-10-skincare-essentials",
  "category": {
    "id": 4,
    "name": "Skincare Routine"
  },
  "tags": ["Skincare", "Summer", "Hydration"],
  "published_at": "2026-06-18T10:00:00Z"
}

Key Architectural Advantages:

• Zero Query-Time JOINs: All category names, tags, and metadata are pre-resolved and stored inside a single document.
• Inverted Indexing: Elasticsearch utilizes inverted indices and n-grams, returning full-text search results in sub-15ms.
• Offloading Database IOPS: Public reader requests hit Elasticsearch directly, protecting MySQL for critical transactional operations.

3. Measured Results: PageSpeed Score Jump from 29 to 69

By shifting article rendering and search queries from MySQL relational JOINs to Elasticsearch document retrieval:

• Google PageSpeed Performance Score: Surged from 29 to 69 (+40 points boost).
• Time to First Byte (TTFB): Accelerated significantly due to instant Elasticsearch index lookup.
• Database CPU Load: Reduced text search read IOPS by over 85%.

Summary & Key Takeaways

• Optimize for Reads: Relational databases excel at write consistency, but flat document engines win for high-concurrency read operations.
• Denormalize Proactively: Denormalizing relational JOINs into pre-aggregated search documents is one of the highest-leverage performance optimizations for content applications.