sqlite 查询速度比较,sqlite速度比较
以下是在 macOS 上使用 SQLPro for SQLite Read Only 版进行的查询速度对比。
select word from words_table where word >= '小' and word < '少' order by wordfrq desc 0.4s
select word,wordfrq from words_table where word >= '小' and word < '少' 未按 wordfrq 排序,0.5s
select word from words_table where word like '小%' 未按 wordfrq 排序,0.48s
select word from words_table where word like '小%' order by wordfrq desc 0.67s
select word,wordfrq from words_table where word like '小%' 0.67s
select word,wordfrq from words_table where word like '小%' limit 100 0.52s
select word,wordfrq from words_table where word like '小%' limit 20 有按 wordfrq 排序 0.52s
select word from words_table where word >= '小' and word < '少' order by wordfrq desc limit 100 0.3s
select word from words_table where word >= '小' and word < '少' order by wordfrq desc limit 20 0.2s
select word,wordfrq from words_table where word glob '小*' limit 20 未按 wordfrq 排序 0.1s
select word from words_table where word glob '小*' order by wordfrq desc limit 20 0.2s
select word from words_table where stroke like '31%y1z32%' 0.86s
select word from words_table where stroke like '31%y1z32%' order by wordfrq desc 0.85s
select word from words_table where stroke like '31%y1z32%' order by wordfrq desc limit 100 0.84s
select word from words_table where stroke like '31%y1z32%' and wordlength == '2' order by wordfrq desc limit 20 0.84s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') 0.41s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc 0.408s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc limit 100 0.405s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc limit 20 0.405s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') and wordlength == '2' order by wordfrq desc limit 20 0.405s
select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%' and wordlength == '2') order by wordfrq desc limit 20 0.38s
select word from words_table where stroke like '31%y1z32%' and stroke in (select stroke from words_table where stroke like '31%' and wordlength == '2') order by wordfrq desc limit 20 0.174s
select word from words_table where stroke >= '31%y1z32' and stroke in (select stroke from words_table where stroke >= '31' and wordlength == '2') order by wordfrq desc limit 20 0.485s
select word from words_table where stroke glob '31*y1z32*' and stroke in (select stroke from words_table where stroke glob '31*' and wordlength == '2') order by wordfrq desc limit 20 0.156s