On Mon, Mar 31, 2014 at 1:11 AM, Joe Nahmias <[email protected]> wrote:
> 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; > > 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 > [something different]... > (1) The LIKE operator treats upper and lower case letters as the same, so unless you have COLLATE NOCASE on the p.code column (which you do not) then "p.code LIKE pref.prefix||'%'" means something very different than "pcode >= pref.prefix AND pcode <= pref.prefix || '~'". (2) There might be wildcard characters '%' and/or '_' in pref.prefix. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
