Thanks for reply ! If I issue this command :
PRAGMA case_sensitive_like = OFF; Then I get the same answer (I mean it uses indexes now): explain query plan select * from companies where post_code like 'rg%'; SEARCH TABLE companies USING INDEX companies_post_code_idx (post_code>? AND post_code<?) It seems strange why sqlite is not using the indexes with the default case_sensitive_like settings, here is the schema: CREATE TABLE companies( ??? ??? id integer primary key, ??? ??? name text collate nocase, ??? ??? number text, ??? ??? care_of text collate nocase, ??? ??? po_box text collate nocase, ??? ??? address text collate nocase, ??? ??? address2 text collate nocase, ??? ??? post_town_id integer, ??? ??? county_id integer, ??? ??? country_id integer, ??? ??? post_code text collate nocase, ??? ??? category_id integer, ??? ??? status_id integer, ??? ??? country_of_origin_id integer, ??? ??? dissolution_date date, ??? ??? incorporation_date date, ??? ??? account_ref_day text, ??? ??? account_ref_month text, ??? ??? account_next_due_date date, ??? ??? account_last_made_up_date date, ??? ??? account_category_id integer, ??? ??? returns_next_due_date date, ??? ??? returns_last_made_up_date date, ??? ??? mortages_charges integer, ??? ??? mortages_outstanding integer, ??? ??? mortages_part_satisfied integer, ??? ??? mortages_satisfied integer, ??? ??? general_partners integer, ??? ??? limited_partners integer ??? ); CREATE INDEX companies_incorporation_date_idx ON companies (incorporation_date); CREATE INDEX companies_name_idx ON companies (name); CREATE INDEX companies_number_idx ON companies (number); CREATE INDEX companies_post_code_idx ON companies (post_code); ? > Mon Apr 04 2016 12:39:35 PM CEST from "Simon Slavin" ><slavins at bigfraud.org> Subject: Re: [sqlite] Difference between sqlite3 >executable and application > > On 4 Apr 2016, at 10:47am, Domingo Alvarez Duarte ><sqlite-mail at dev.dadbiz.es> wrote: > > >>It would be a gross mistake if the databases weren't the same. >> >> It's an existing and populated database. >> > The only thing that comes to mind is that one app is using case sensitive >and the other is not. Unfortunately the command to check the status seems to >be missing from the documentation and therefore possibly missing from SQLite. > > In both apps, before executing > > select * from companies where post_code like 'rg%' > > execute > > PRAGMA case_sensitive_like = ON > > and see what happens. Then perhaps try it off. > > <https://www.sqlite.org/pragma.html#pragma_case_sensitive_like> > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
