SQL: Composite Indexing for Sub-Millisecond Queries

Optimize multi-million row backlink lookup tables from 2.5s down to 0.003s using Leftmost Prefix B-Tree indexes.

B-Tree Composite Indexes and the Leftmost Prefix Rule

When querying tables with millions of rows:

SELECT * FROM sfx_links WHERE target_domain = 'seoffix.com' AND status_code = 200 ORDER BY created_at DESC;

Creating separate indexes on target_domain, status_code, and created_at forces the MySQL query optimizer to perform index merge passes or filesort memory buffers.

A **Composite Index** on (target_domain, status_code, created_at) allows the engine to locate matching rows in a single B-Tree descent and stream rows pre-sorted with 0 filesort penalty.

Target Output

Query OK, 0 rows affected. Index 'idx_links_target_status_created' created.

Need a Hint?

Place exact-match equality filters first: target_domain, status_code
Place the range or ORDER BY column last: created_at DESC
This satisfies the Leftmost Prefix B-Tree traversal rule

solution.js

Terminal Output Ready

Click "Run Code & Test" to execute your solution.