How SQLiteLint Detects Hidden Performance Issues in Mobile Apps

Introduction

Matrix is WeChat's self‑developed APM system.

SQLite is widely used on mobile; its quality directly affects user experience. WeChat heavily uses SQLite, so monitoring its quality is essential.

Typical post‑release monitoring relies on latency metrics or user feedback, which has drawbacks: problems are discovered after impact, and marginally slow queries may be ignored.

Can we monitor SQLite usage quality before release? We built SQLiteLint, a runtime analysis tool that applies best‑practice rules to detect potential issues early.

This article explains SQLiteLint’s design and shares SQLite usage experience.

Overview

SQLiteLint runs in the app, independent of data volume. When a SQL statement executes, it reviews the statement for problems, usable during development, testing, or gray‑release.

Detection flow:

Collect runtime SQL execution info (statements, table info via PRAGMA). For WCDB, use setSQLiteTrace; for Android default, hook sqlite3_profile.

Preprocess: build syntax tree, generate parameter‑less SQL, detect SELECT * etc., in a separate thread.

Schedule specific checkers (C++ algorithms) for each statement (on‑demand, sampling, init, per‑statement).

Report issues (log or UI prompt).

Detected Issues Overview

1. Index usage problems

Indexes are the most common performance issue. SQLiteLint analyses the query plan (SCAN TABLE, SEARCH TABLE, USE TEMP B‑TREE) and checks for:

Missing index causing full table scan.

Index present but not used.

Unnecessary temporary B‑tree sorting.

Insufficient composite index.

False‑positive handling.

Examples with images illustrate each case and how to fix them (create appropriate index, rewrite LIKE, use IN, etc.).

2. Redundant index detection

SQLiteLint scans all tables at startup and flags indexes that are prefixes of larger indexes, recommending keeping only the most comprehensive one.

3. SELECT * detection

Scanning the SQL AST, SQLiteLint warns against SELECT * because it prevents covering index usage and increases memory consumption on Android.

4. AUTOINCREMENT detection

Uses SQLite documentation to advise avoiding AUTOINCREMENT due to extra CPU, memory, disk space, and I/O overhead.

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

5. Prepared statement recommendation

Detects repeated similar statements without prepared statements and suggests using them for performance and security.

6. without rowid recommendation

For tables with non‑integer or composite primary keys and small row size, SQLiteLint suggests the WITHOUT ROWID optimization, explaining its space and time benefits.

Conclusion

SQLiteLint enables early detection of SQLite quality issues, promotes best practices, and reduces post‑release problems. The tool will be open‑sourced soon.