BlogApp Index Strategy — Designing and Validating the Complete Index Set

Designing the BlogApp’s complete index strategy requires understanding the actual query patterns — which queries run most frequently, which are most expensive, and which indexes provide the highest benefit. The approach is systematic: audit existing indexes, identify missing ones from execution plans and DMVs, design covering indexes for the top queries, and validate with logical read counts. The goal is to eliminate all key lookups and table scans from the application’s hot paths.

Complete BlogApp Index Strategy #

-- ── Step 1: Audit existing indexes ────────────────────────────────────────
SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc,
    i.is_unique,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    -- Reads vs writes ratio:
    (us.user_seeks + us.user_scans + us.user_lookups) AS TotalReads,
    us.user_updates AS TotalWrites
FROM   sys.indexes i
JOIN   sys.tables  t  ON t.object_id  = i.object_id
LEFT   JOIN sys.dm_db_index_usage_stats us
    ON  us.object_id = i.object_id
    AND us.index_id  = i.index_id
    AND us.database_id = DB_ID()
WHERE  t.name IN ('Posts', 'Users', 'Tags', 'PostTags', 'Comments')
ORDER  BY t.name, TotalReads DESC;
-- Low reads + high writes = candidate for removal

-- ── Step 2: BlogApp final index set ──────────────────────────────────────

-- POSTS TABLE:
-- 1. Clustered (auto via PK)
-- 2. Published posts list — the most frequent query
CREATE NONCLUSTERED INDEX IX_Posts_Published_Date
ON dbo.Posts (IsPublished, PublishedAt DESC)
INCLUDE (Title, Slug, Excerpt, ViewCount, CommentCount, IsFeatured,
         AuthorId, CategoryId, CoverImageUrl)
WHERE IsPublished = 1;   -- filtered: only published rows

-- 3. Slug lookup — every post page view
CREATE UNIQUE NONCLUSTERED INDEX UX_Posts_Slug
ON dbo.Posts (Slug)
INCLUDE (Id, Title, Body, Excerpt, ViewCount, IsPublished, AuthorId, CategoryId);

-- 4. Author's posts — admin post list
CREATE NONCLUSTERED INDEX IX_Posts_AuthorId_Date
ON dbo.Posts (AuthorId, PublishedAt DESC)
INCLUDE (Title, Slug, Status, ViewCount, IsPublished);

-- USERS TABLE:
-- 5. Email/normalized email lookup — login
CREATE UNIQUE NONCLUSTERED INDEX UX_Users_NormalizedEmail
ON dbo.Users (NormalizedEmail)
INCLUDE (Id, DisplayName, AvatarUrl, IsActive);

-- POSTTAGS TABLE:
-- 6. Find posts by tag
CREATE NONCLUSTERED INDEX IX_PostTags_TagId
ON dbo.PostTags (TagId, PostId);
-- PK (PostId, TagId) already covers the reverse lookup

-- TAGS TABLE:
-- 7. Tag slug lookup
CREATE UNIQUE NONCLUSTERED INDEX UX_Tags_Slug
ON dbo.Tags (Slug) INCLUDE (Id, Name);

-- COMMENTS TABLE:
-- 8. Comments per post
CREATE NONCLUSTERED INDEX IX_Comments_PostId_Approved
ON dbo.Comments (PostId, IsApproved, CreatedAt DESC)
INCLUDE (AuthorId, Body);

-- ── Step 3: Validate with logical reads ───────────────────────────────────
SET STATISTICS IO ON;

-- Published posts list query:
SELECT TOP 10 * FROM dbo.vw_PublishedPostSummary ORDER BY PublishedAt DESC;
-- Target: < 10 logical reads (index seek on filtered index)
-- Before indexes: 342 logical reads (table scan)
-- After indexes:  3 logical reads (index seek, no key lookup)

-- Slug lookup:
SELECT * FROM dbo.Posts WHERE Slug = 'getting-started-dotnet';
-- Target: 2-3 logical reads (single page seek on unique index)

SET STATISTICS IO OFF;

Common Mistakes #

Mistake 1 — Keeping unused indexes (zero seeks, high update cost) #

❌ Wrong — 3-year-old index with 0 seeks and 2 million updates still in place; pure overhead on every write.

✅ Correct — audit with sys.dm_db_index_usage_stats; drop indexes with no reads after a full business cycle observation.

Mistake 2 — Not validating indexes with actual logical read measurements #

❌ Wrong — adding indexes without measuring reads before/after; optimizer may not use the new index.

✅ Correct — always measure SET STATISTICS IO ON before and after index changes on real data volumes.