Database Indexing 101: How to Stop Slow SQL Queries Before They Kill Your App

ashis jenisharajendran — Mon, 09 Nov 2026 13:14:55 +0000

Have you ever deployed a feature that worked fine in development, only to watch it grind your production database to a halt once real users started pouring in? 9 times out of 10, the culprit is missing or improper database indexing.

Let's demystify how indexing works under the hood and how you can write queries that scale effortlessly.

How Databases Read Data Without an Index
Imagine a database table as a 500-page physical book with no index at the back. If you want to find a specific user by email (SELECT * FROM users WHERE email = 'alice@example.com'), the database has to perform a Sequential Scan (Full Table Scan). It starts reading page 1, row 1, and checks every single row until it finds a match.

For 100 rows, this takes microseconds. For 10,000,000 rows, your CPU spikes, and your query takes seconds.

How an Index Changes the Game
An index is a separate data structure (typically a B-Tree) managed by the database.

The Analogy: Think of an index like the alphabetical index at the back of a textbook. Instead of scanning every page, you look up the key in the index, find the exact page number, and jump straight to it.

The Trade-off: While indexes make reads (SELECT) blazing fast, they add overhead to writes (INSERT, UPDATE, DELETE) because the database has to update the index tree every time data changes. Index wisely!

Best Practices for Designing Indexes
Strategy When to Use What to Avoid
High-Cardinality Columns Columns with unique or highly distinct values (e.g., user_id, email, uuid). Low-cardinality columns with very few unique values (e.g., a status column with just true or false).
Composite (Multi-Column) Indexes When you frequently filter by multiple columns together (e.g., WHERE status = 'active' AND created_at > ...). Putting columns in the wrong order. The leftmost prefix rule applies; put the most selective column first.
Foreign Keys Always index your foreign key columns (company_id, author_id) to speed up JOIN operations. Over-indexing. Creating an index for every single column will bloat your disk space and slow down write operations.
Real-World Example: Using EXPLAIN
Before blindly adding indexes, use your database's EXPLAIN query planner prefix to see what it's doing:

SQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
If the output says Seq Scan on orders, your database is reading the whole table. Once you add an index:

SQL
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Running EXPLAIN again will show Index Scan using idx_orders_customer_id, confirming your query is now optimized.

Conclusion
Database tuning doesn't have to be black magic. By identifying your most frequent query patterns, utilizing B-Tree indexes on high-cardinality and foreign key columns, and leveraging EXPLAIN, you can keep your application fast and responsive under heavy loads.

Mastering Core Web Vitals: A Practical Guide to Boosting Frontend Performance

ashis jenisharajendran — Mon, 09 Nov 2026 13:11:42 +0000

hen building modern web applications, functionality is only half the battle. If your site takes more than three seconds to load or feels sluggish when clicking around, users will bounce—and search engines will penalize your rankings.

Enter Core Web Vitals: Google’s standardized metrics for measuring real-world user experience. In this guide, we will break down what these metrics mean and how you can optimize your frontend code to ace them.

Understanding the Core Trio
Google focuses on three primary pillars of user experience:

LCP (Largest Contentful Paint): Measures loading performance. It marks the point when the main page content has likely loaded. A good LCP is under 2.5 seconds.

INP (Interaction to Next Paint): Measures responsiveness. It assesses how long the page takes to react when a user interacts with it (clicks, taps, or key presses). A good INP is under 200 milliseconds.

CLS (Cumulative Layout Shift): Measures visual stability. It tracks unexpected layout shifts while the page is loading (e.g., when an ad pops in and pushes your text down). A good CLS score is less than 0.1.

Step-by-Step Optimization Techniques

Optimize Your LCP (Loading) Focus on the critical rendering path: Compress and modernize images. Serve images in next-gen formats like WebP or AVIF and use srcset to serve appropriately sized images based on the user's viewport.

Preload Critical Assets: Add tags for your primary hero images, critical fonts, or above-the-fold stylesheets so the browser fetches them immediately.

Streamline Server Response Times: Leverage a Content Delivery Network (CDN) and caching to keep your Time to First Byte (TTFB) low.

Improve INP (Responsiveness) Keep the main thread unblocked: JavaScript runs on a single thread. If a script takes more than 50ms to execute, it blocks user input. Use requestIdleCallback or break tasks up using setTimeout.

Defer Non-Essential JavaScript: Load analytics, tracking scripts, and heavy widgets asynchronously using async or defer.

Eliminate CLS (Visual Stability) Prevent content from jumping around: Always include explicit width and height attributes on your and elements, or reserve space using CSS Aspect Ratios (aspect-ratio: 16/9).

Avoid Dynamic Content Injection: Never insert content above existing content dynamically unless it's a direct response to a user action like a click.

Conclusion
Improving your Core Web Vitals isn't just about chasing a high score on Lighthouse—it results in a tangible boost in conversion rates, user retention, and SEO rankings. Pick one metric to audit today, measure your baseline, and start optimizing!

DEV Community: ashis jenisharajendran

Database Indexing 101: How to Stop Slow SQL Queries Before They Kill Your App

Mastering Core Web Vitals: A Practical Guide to Boosting Frontend Performance