如何通过索引优化SQLite中标签统计SQL语句的执行速度？

先修正SQL中的冗余字段 #

你当前SELECT语句里的created_at是冗余的——GROUP BY tag后，该字段返回的是分组内任意一条记录的创建时间，对统计结果毫无意义。保留它会迫使数据库额外读取或回表获取该字段数据，直接拖慢查询。修改后的SQL：

SELECT tag, COUNT(tag) AS count
FROM tags 
WHERE created_at >= strftime('%Y-%m-%d', 'now', '-1 days') 
GROUP BY tag 
ORDER BY count DESC 
LIMIT 100

针对查询逻辑优化索引 #

你现有的idx_tags_created_at__tag（created_at, tag）索引能快速过滤出近一天的数据，但过滤后的数据中tag是按created_at排序的，同一tag的记录分散在不同时间区间，GROUP BY时需要额外做聚合排序，数据量大时效率极低。可以根据数据分布选择以下两种优化索引：

方案1：优化为覆盖索引（适合每日数据量极大的场景）

保留created_at作为索引前缀，确保快速过滤时间范围，同时让索引包含tag（已满足），这样查询无需回表，直接从索引完成聚合：

-- 若已存在原索引，可直接使用；若需重建则执行：
CREATE INDEX IF NOT EXISTS idx_tags_created_at_tag_cover ON tags(created_at, tag);

该索引能让数据库快速定位到近一天的所有记录，直接从索引中提取tag进行聚合，避免回表开销。

方案2：调整索引顺序（适合标签总数较少的场景）

将索引前缀改为tag，后缀为created_at，这样同一tag的记录在索引中是连续存储的，数据库可以逐个遍历tag，快速统计符合时间条件的记录数，无需额外聚合排序：

CREATE INDEX IF NOT EXISTS idx_tags_tag_created_at ON tags(tag, created_at);

这种索引下，数据库会按tag分组遍历，对每个tag直接统计created_at >= 目标时间的记录数，聚合效率更高。

额外小优化 #

将时间条件中的strftime('%Y-%m-%d', 'now', '-1 days')改为datetime('now', '-1 days')，避免字符串与日期类型的隐式转换，进一步提升过滤效率：

SELECT tag, COUNT(tag) AS count
FROM tags 
WHERE created_at >= datetime('now', '-1 days') 
GROUP BY tag 
ORDER BY count DESC 
LIMIT 100

内容的提问来源于stack exchange，提问作者chovy