SQL Index Failure Scenarios: Essential Pitfalls and Fixes Every Developer Must Know

1. Index Underlying Principle: B+Tree Basics

MySQL InnoDB uses a B+Tree structure for indexes. All data rows reside in leaf nodes, while non‑leaf nodes store only keys and pointers, enabling efficient range queries. Leaf nodes are linked, and the tree height is typically 3‑4 levels, so a million rows require only 3‑4 disk I/Os.

All data stored in leaf nodes; non‑leaf nodes hold keys and pointers.

Leaf nodes linked for fast range scans.

Height 3‑4 levels means millions of rows need only a few I/Os.

2. Index Failure Scenario Classification

2.1 Function Operations on Indexed Columns

Failure Reason: The B+Tree stores the raw column values. Applying a function (e.g., DATE(column)) destroys the ordered property, forcing a full table scan.

// Wrong example: using DATE() on an indexed column
func QueryOrdersByDateWrong(db *sql.DB, dateStr string) ([]Order, error) {
    query := "SELECT * FROM orders WHERE DATE(create_time) = ?"
    rows, err := db.Query(query, dateStr)
    if err != nil { return nil, err }
    defer rows.Close()
    var orders []Order
    for rows.Next() {
        var order Order
        if err := rows.Scan(&order.ID, &order.OrderNo, &order.Amount, &order.CreateTime, &order.Status); err != nil {
            return nil, err
        }
        orders = append(orders, order)
    }
    return orders, nil
}

// Correct example: use a range query to keep the index usable
func QueryOrdersByDateCorrect(db *sql.DB, dateStr string) ([]Order, error) {
    startTime := dateStr + " 00:00:00"
    endTime   := dateStr + " 23:59:59"
    query := "SELECT * FROM orders WHERE create_time >= ? AND create_time <= ?"
    rows, err := db.Query(query, startTime, endTime)
    if err != nil { return nil, err }
    defer rows.Close()
    var orders []Order
    for rows.Next() {
        var order Order
        if err := rows.Scan(&order.ID, &order.OrderNo, &order.Amount, &order.CreateTime, &order.Status); err != nil {
            return nil, err
        }
        orders = append(orders, order)
    }
    return orders, nil
}

Common functions that cause this issue include DATE(col), YEAR(col), UPPER(col), LOWER(col), string concatenation, and arithmetic expressions.

2.2 Implicit Type Conversion

Failure Reason: When the query value type differs from the indexed column type, MySQL must cast values, which prevents index usage.

// Wrong: passing an interface{} that may be int for a VARCHAR column
func QueryUserByPhoneWrong(db *sql.DB, phone interface{}) ([]User, error) {
    query := "SELECT * FROM users WHERE phone = ?"
    rows, err := db.Query(query, phone)
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

// Correct: ensure the parameter type matches the column type
func QueryUserByPhoneCorrect(db *sql.DB, phone int64) ([]User, error) {
    query := "SELECT * FROM users WHERE phone = ?"
    rows, err := db.Query(query, strconv.FormatInt(phone, 10))
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

2.3 LIKE Wildcard Position

Failure Reason: B+Tree indexes support prefix matching. If the wildcard % appears at the beginning or middle of the pattern, the index cannot be used.

func AnalyzeLikePatterns() []string {
    patterns := []struct{ pattern, result string }{
        {"张%", "Index works – prefix match"},
        {"%三", "Index fails – suffix match"},
        {"%张%", "Index fails – middle match"},
        {"张_", "Index works – single‑char wildcard"},
    }
    var results []string
    for _, p := range patterns {
        results = append(results, fmt.Sprintf("LIKE '%s': %s", p.pattern, p.result))
    }
    return results
}

Optimization suggestions for required suffix matches include using FULLTEXT indexes, external search engines (e.g., Elasticsearch), or storing a reversed string column with a prefix index.

2.4 Improper Use of OR Conditions

Failure Reason: If any operand in an OR expression lacks an index, the optimizer abandons index usage for the whole condition.

// Wrong: OR mixes indexed and non‑indexed columns
func QueryUsersWithORWrong(db *sql.DB, userID, age int) ([]User, error) {
    query := "SELECT * FROM users WHERE user_id = ? OR age = ?"
    rows, err := db.Query(query, userID, age)
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

// Correct: split into two queries and UNION ALL
func QueryUsersWithUnionCorrect(db *sql.DB, userID, age int) ([]User, error) {
    query := `
        SELECT * FROM users WHERE user_id = ?
        UNION ALL
        SELECT * FROM users WHERE age = ?
    `
    rows, err := db.Query(query, userID, age)
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

2.5 Composite Index Left‑Most Prefix Rule

Failure Reason: A composite B+Tree index is ordered by the defined column sequence. Queries must start with the left‑most column; skipping it makes the index unusable.

type IndexScenario struct { Query, Result, Reason string }

func AnalyzeCompositeIndexUsage() []IndexScenario {
    return []IndexScenario{
        {"WHERE user_id = 1001 AND status = 1 AND create_time > '2024-01-01'", "All fields effective", "Exact left‑most match"},
        {"WHERE user_id = 1001 AND status = 1", "First two fields effective", "Matches left N columns"},
        {"WHERE status = 1 AND create_time > '2024-01-01'", "Index completely fails", "Skipped leftmost user_id"},
        {"WHERE user_id = 1001 AND create_time > '2024-01-01'", "Only user_id works", "Skipped middle status"},
    }
}

Range Query Truncation Effect: Once a range operator ( >, <, BETWEEN) is used on a column, subsequent columns in the composite index cannot be used.

type RangeQueryResult struct { Query, Result string }

func AnalyzeRangeQueryEffects() []RangeQueryResult {
    return []RangeQueryResult{
        {"WHERE a = 10 AND b = 20 AND c > 30", "a,b,c all effective"},
        {"WHERE a = 10 AND b > 20 AND c = 30", "Only a,b effective; c fails"},
    }
}

2.6 Data Distribution Impact on Optimizer Decisions

Failure Reason: The optimizer estimates plan cost from column statistics. Low selectivity leads it to prefer a full scan.

type SelectivityStats struct {
    ColumnName   string
    DistinctCount int
    TotalCount    int
    Selectivity  float64
}

func CalculateIndexSelectivity(db *sql.DB, tableName, columnName string) (*SelectivityStats, error) {
    distinctQuery := fmt.Sprintf("SELECT COUNT(DISTINCT %s) FROM %s", columnName, tableName)
    var distinctCount int
    if err := db.QueryRow(distinctQuery).Scan(&distinctCount); err != nil { return nil, err }
    totalQuery := fmt.Sprintf("SELECT COUNT(*) FROM %s", tableName)
    var totalCount int
    if err := db.QueryRow(totalQuery).Scan(&totalCount); err != nil { return nil, err }
    sel := 0.0
    if totalCount > 0 { sel = float64(distinctCount) / float64(totalCount) }
    return &SelectivityStats{ColumnName: columnName, DistinctCount: distinctCount, TotalCount: totalCount, Selectivity: sel}, nil
}

Empirical thresholds:

Selectivity > 30% → index works well.

5%–30% → depends on query frequency.

Selectivity < 5% → usually avoid index.

2.7 ORDER BY / GROUP BY Order Mismatch

Failure Reason: When the ORDER BY or GROUP BY column order or sort direction differs from the composite index, MySQL cannot use the index ordering and adds a filesort.

type OrderByScenario struct { Query, Extra, Result string }

func AnalyzeOrderByIndex() []OrderByScenario {
    return []OrderByScenario{
        {"ORDER BY status, create_time DESC", "Using index", "Index sort"},
        {"ORDER BY status, create_time ASC",  "Using filesort", "Extra sort needed"},
        {"ORDER BY create_time, status",    "Using filesort", "Order mismatch"},
    }
}

3. Production Case Analyses

3.1 E‑commerce Order Query Optimization

Problem: An orders table with 8 million rows runs the query

SELECT * FROM orders WHERE status='PAID' AND DATE(create_time)='2024-01-01'

in over 3 seconds.

Root Causes:

Function DATE(create_time) disables the index on create_time. status has low selectivity (only five possible values).

Using SELECT * forces a table‑row lookup (back‑table).

Solution: Avoid the function, use a range on the raw timestamp, create a covering index, and select only needed columns.

func OptimizeOrderQuery(db *sql.DB, queryDate string) ([]Order, error) {
    // 1. Avoid function, use range
    startTime := queryDate + " 00:00:00"
    endTime   := queryDate + " 23:59:59"
    // 2. Covering index (status, create_time, order_no, amount)
    // 3. Select only required fields
    query := `
        SELECT order_no, amount, create_time
        FROM orders
        WHERE status = 'PAID'
        AND create_time >= ?
        AND create_time <= ?
    `
    rows, err := db.Query(query, startTime, endTime)
    if err != nil { return nil, err }
    defer rows.Close()
    var orders []Order
    for rows.Next() {
        var order Order
        if err := rows.Scan(&order.OrderNo, &order.Amount, &order.CreateTime); err != nil {
            return nil, err
        }
        orders = append(orders, order)
    }
    return orders, nil
}

Result: Query time drops from 3.2 seconds to 85 milliseconds, a ~37× speedup.

3.2 User Search Feature Optimization

Problem: A 12 million‑row user table executes

SELECT * FROM users WHERE name LIKE '%张%' OR email LIKE '%张%'

slowly.

Optimization Options:

Full‑text index: MATCH(name,email) AGAINST(? IN BOOLEAN MODE).

Redundant reverse column with a prefix index for suffix matches.

func OptimizeUserSearch(db *sql.DB, keyword string) ([]User, error) {
    // Option 1: Full‑text search
    queryFulltext := `
        SELECT id, name, email
        FROM users
        WHERE MATCH(name, email) AGAINST(? IN BOOLEAN MODE)
    `
    // Option 2: Reverse column for suffix matching
    queryReverse := `
        SELECT id, name, email
        FROM users
        WHERE reverse_name LIKE ?
    `
    reverseKeyword := reverseString(keyword) + "%"
    rows, err := db.Query(queryFulltext, keyword) // choose one of the two queries
    if err != nil { return nil, err }
    defer rows.Close()
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

func reverseString(s string) string {
    runes := []rune(s)
    for i, j := 0, len(runes)-1; i < j; i, j = i+1, j-1 {
        runes[i], runes[j] = runes[j], runes[i]
    }
    return string(runes)
}

4. Index Maintenance and Optimization Strategies

4.1 Regularly Update Statistics

The optimizer relies on up‑to‑date statistics to generate optimal plans.

func UpdateTableStatistics(db *sql.DB, tableName string) error {
    query := fmt.Sprintf("ANALYZE TABLE %s", tableName)
    var tbl, op, msgType, message string
    if err := db.QueryRow(query).Scan(&tbl, &op, &msgType, &message); err != nil { return err }
    fmt.Printf("Statistics updated: table=%s op=%s msg=%s
", tbl, op, message)
    return nil
}

4.2 Index Fragmentation Management

Insert, update, and delete operations cause fragmentation, degrading performance.

type IndexFragmentation struct { IndexName string; FragmentationRate float64; PageCount int }

func AnalyzeIndexFragmentation(db *sql.DB, tableName string) ([]IndexFragmentation, error) {
    query := fmt.Sprintf(`
        SELECT index_name,
               ROUND(avg_fragmentation_in_percent,2) AS fragmentation_rate,
               page_count
        FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('%s'), NULL, NULL, 'LIMITED')
        WHERE avg_fragmentation_in_percent > 5
        ORDER BY avg_fragmentation_in_percent DESC
    `, tableName)
    rows, err := db.Query(query)
    if err != nil { return nil, err }
    defer rows.Close()
    var frags []IndexFragmentation
    for rows.Next() {
        var f IndexFragmentation
        if err := rows.Scan(&f.IndexName, &f.FragmentationRate, &f.PageCount); err != nil { return nil, err }
        frags = append(frags, f)
    }
    return frags, nil
}

func RebuildIndex(db *sql.DB, tableName, indexName string) error {
    query := fmt.Sprintf("ALTER INDEX %s ON %s REBUILD", indexName, tableName)
    _, err := db.Exec(query)
    return err
}

Fragmentation handling policy:

5%–30% → reorganize index (REORGANIZE).

>30% → rebuild index (REBUILD).

Tables < 10 k rows → usually no maintenance needed.

4.3 Best Practices for Index Design

Composite Index Field Order: Place high‑frequency, high‑selectivity columns first; equality columns before range columns.

Avoid Redundant Indexes: A composite index (a,b) can cover a single‑column index (a).

Small Tables: For < 10 k rows, indexes may add overhead and can be omitted.

Use Covering Indexes: Include all queried columns in the index to eliminate back‑table lookups.

Monitor Index Usage: Periodically identify and drop unused indexes.

type IndexUsage struct {
    TableName   string
    IndexName   string
    IndexType   string
    UserSeeks   int
    UserScans   int
    UserLookups int
    UserUpdates int
    LastUserSeek  *time.Time
    LastUserScan  *time.Time
}

func AnalyzeIndexUsage(db *sql.DB) ([]IndexUsage, error) {
    query := `
        SELECT OBJECT_NAME(s.object_id) AS table_name,
               i.name AS index_name,
               i.type_desc AS index_type,
               s.user_seeks, s.user_scans, s.user_lookups, s.user_updates,
               s.last_user_seek, s.last_user_scan
        FROM sys.dm_db_index_usage_stats s
        INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
        WHERE s.database_id = DB_ID() AND OBJECT_NAME(s.object_id) IS NOT NULL
        ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC
    `
    rows, err := db.Query(query)
    if err != nil { return nil, err }
    defer rows.Close()
    var usages []IndexUsage
    for rows.Next() {
        var u IndexUsage
        if err := rows.Scan(&u.TableName, &u.IndexName, &u.IndexType, &u.UserSeeks, &u.UserScans, &u.UserLookups, &u.UserUpdates, &u.LastUserSeek, &u.LastUserScan); err != nil { return nil, err }
        usages = append(usages, u)
    }
    return usages, nil
}

5. Summary

SQL index failure is a core challenge in database performance tuning. The article analyzed seven failure scenarios and their underlying mechanisms:

Function Operations: Break B+Tree ordering; avoid functions on indexed columns.

Implicit Type Conversion: Keep query value types consistent with indexed column types.

LIKE Wildcard Position: Prefix matches work; suffix or middle wildcards cause failure.

Improper OR Usage: Ensure every OR operand is indexed or split queries.

Left‑Most Prefix Rule: Composite indexes must be matched from the leftmost column.

Data Distribution Impact: Low‑selectivity columns provide little benefit.

Order/Group Mismatch: ORDER BY/GROUP BY must follow index column order and direction.

Key recommendations:

Always run EXPLAIN to inspect execution plans.

Build covering indexes to avoid back‑table lookups.

Regularly maintain statistics and defragment indexes.

Design indexes based on actual query patterns; avoid blind additions.

Monitor index usage in production and prune unused indexes.

Understanding the low‑level B+Tree behavior and the specific failure mechanisms enables developers to avoid common performance traps and build efficient, stable database systems.