sqlite 不使用索引与类似查询 [英] sqlite not using index with like query

问题描述

我一直在尝试让 sqlite 使用带有 like 的索引但无济于事.我试过整理 nocase ，但仍然没有运气.任何人都对如何让 sqlite 像点击索引一样有任何想法.提前致谢

Ive been trying to get sqlite to use an index with a like to no avail. Ive tried collate nocase and still no luck. Anyone have any ideas on how to get sqlite to do a like hitting an index. Thanks in advance

DROP TABLE IF EXISTS "test";
DROP TABLE IF EXISTS "test2";
DROP TABLE IF EXISTS "test3";
create table test(name TEXT COLLATE NOCASE);
create table test2(name TEXT);
create table test3(name TEXT);
create index idx_test_name on test(name);
create index idx_test2_name on test2(name);
create index idx_test3_name on test3(name COLLATE NOCASE);
insert into test(name) values('dan');
insert into test2(name) values('dan');
insert into test3(name) values('dan');
--explain query plan select * from test where name like 'test%' 
-- explain query plan select * from test2 where name like 'test%' 
-- explain query plan select * from test3 where name like 'test%' 

推荐答案

在 SQLite 3.6.23.1 中，使用了 test 上的索引:

In SQLite 3.6.23.1, the index on test is used:

> explain query plan select * from test where name like 'test%';
TABLE test WITH INDEX idx_test_name

> explain query plan select * from test2 where name like 'test%';
TABLE test2

> explain query plan select * from test3 where name like 'test%';
TABLE test3

对于 SQLite 3.7.15 的开发版本，同时使用 test 和 test3 的索引(test2 上的索引)用于扫描，而不是搜索):

With a development version of SQLite 3.7.15, both test's and test3's indexes are used (the index on test2 is used for scanning, not searching):

> explain query plan select * from test where name like 'test%';
SEARCH TABLE test USING COVERING INDEX idx_test_name (name>? AND name<?) (~31250 rows)

> explain query plan select * from test2 where name like 'test%';
SCAN TABLE test2 USING COVERING INDEX idx_test2_name (~500000 rows)

> explain query plan select * from test3 where name like 'test%';
SEARCH TABLE test3 USING COVERING INDEX idx_test3_name (name>? AND name<?) (~31250 rows)

所以答案是更新 SQLite.

So the answer is to update SQLite.

这篇关于sqlite 不使用索引与类似查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！