Hello list, I have the following schema in SQLite3 (running on Debian unstable):
.version SQLite 3.8.3.1 2014-02-11 14:52:19 ea3317a4803d71d88183b29f1d3086f46d68a00e CREATE TABLE icd10pcs_order ( year int, order_num int, code varchar(7), is_hipaa_valid bool, short_desc varchar(60), long_desc text ); CREATE UNIQUE INDEX IX_order_year_code ON icd10pcs_order(year DESC, code ASC); CREATE TABLE icd10pcs_prefixes ( year int, prefix varchar(3), section varchar(50), pos2_label varchar(50), pos3_label varchar(50), pos2_title varchar(50), pos3_title varchar(50), pos4_title varchar(50), pos5_title varchar(50), pos6_title varchar(50), pos7_title varchar(50), definition varchar(255) ); CREATE UNIQUE INDEX IX_i10pcs_year_prefix ON icd10pcs_prefixes(year DESC, prefix ASC); SQLite correctly uses the index if my query looks like this: 1: SELECT 2: COUNT(*) AS Num, pref.year, pref.prefix, pref.section, pref.pos2_title, 3: pref.pos2_label, pref.pos3_title, pref.pos3_label, pref.definition 4: FROM icd10pcs_prefixes pref 5: INNER JOIN icd10pcs_order p ON p.year = pref.year 6: AND p.code >= pref.prefix AND p.code <= pref.prefix || '~' 7: WHERE pref.year = 2014 8: GROUP BY pref.prefix 9: HAVING COUNT(*) >= 200 A: ORDER BY Num; Then I get a query plan that looks like: 0|0|0|SEARCH TABLE icd10pcs_prefixes AS pref USING INDEX IX_i10pcs_year_prefix (year=?) 0|1|1|SEARCH TABLE icd10pcs_order AS p USING COVERING INDEX IX_order_year_code (year=? AND code>? AND code<?) 0|0|0|USE TEMP B-TREE FOR ORDER BY However, if I change line 6 to: AND p.code LIKE pref.prefix || '%' I expect SQLite3 to perform an optimization on the LIKE predicate and continue to use the full IX_order_year_code index. Instead, I see the following query plan: 0|0|0|SEARCH TABLE icd10pcs_prefixes AS pref USING INDEX IX_i10pcs_year_prefix (year=?) 0|1|1|SEARCH TABLE icd10pcs_order AS p USING COVERING INDEX IX_order_year_code (year=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY Note that the index IX_order_year_code is not being searched by the code field, even though the predicate is a simple prefix that should be able to be translated to a range scan (similar to my query above). It seems to me, after reading <https://www.sqlite.org/optoverview.html#like_opt>, that something is causing SQLite to miss this optimization. Can anyone shed some light on why this is not working? Do I need to open a bug report / feature request for this? Thanks, --Joe _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
