Not to be flippant, but how could SQLite optimize for the output of a custom function you define outside of SQLite? It can't possibly know anything about how IS_MY_BIRTHDAY() works. Your function could return 1 for every single row, or for no rows at all, without regard to whether you're passing the function an indexed column.
-scott On Mon, Oct 5, 2009 at 9:01 AM, Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote: > > This makes sense. Though, I think the documentation should cover this. > > > > Thanks for your response. > >> From: paiva...@gmail.com >> Date: Mon, 5 Oct 2009 09:31:10 -0400 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] "Stored procedures" performance issue >> >> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this >> > function, if I encounter a date greater than my birthday, then I want to >> > tell SQLite to stop searching, since the date is indexed. >> > >> > Is this possible? If so, how? >> >> Even if this was possible it would be useless because you cannot say >> if SQLite traverses table via index or makes just full table scan. And >> in your particular case SQLite will see that there's no benefit in >> using any index and will just scan the whole table. >> The general advice in this case is to add column to the table which >> will store the result of your function, create an index on that >> additional column and then use that column in the query. In this case >> SQLite will understand that it's better to use index and will use it. >> >> Pavel >> >> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson >> <kristoffer.daniels...@live.se> wrote: >> > >> > When I create my own "stored procedures" using sqlite3_create_function, I >> > get horrible performance (which I expected) even though the column of >> > interest is INDEXED. >> > >> > >> > Consider this sample (it's stupid, but it shows my problem): >> > >> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); >> > >> > >> > >> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this >> > function, if I encounter a date greater than my birthday, then I want to >> > tell SQLite to stop searching, since the date is indexed. >> > >> > >> > >> > Is this possible? If so, how? >> > >> > >> > Thanks. >> > >> > _________________________________________________________________ >> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och >> > Digg när de skickar e-post till dig. >> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 >> > _______________________________________________ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _________________________________________________________________ > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg > när de skickar e-post till dig. > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
