[sqlite] 3.7.3 -> 3.7.8 changes

[Soundfaction]

I just upgraded SQLITE to 3.7.8 from 3.7.3. 

I have found queries I was previously doing have become extremely slow, and
wondered if its a problem with my understanding of SQL or a bug in the QUERY
PLAN in newer versions: I wouldn't expect query times to change from 100ms
to several minutes due to an update. If my SQL is poor then I'd love to know
what is wrong with it and how I can therefore avoid making the same mistakes
in future (and why it worked in 3.7.3 ok!)

The query is below along with EXPLAIN QUERY PLAN output for 3.7.8. and
3.7.3, and the re-factored query that is fast in both versions, but should
result in the same query plan (I thought!) and does so in 3.7.3

-
EXPLAIN QUERY PLAN SELECT tabEmailHeaders.* from
tabEmailAddress,tabRcptAddr,tabEmailHeaders,tabStore WHERE
(tabEmailAddress.szAddress='a...@b.com' OR 
tabEmailAddress.szAddress='c...@b.com'
OR tabEmailAddress.szAddress='d...@b.com' OR
tabEmailAddress.szAddress='e...@b.com') AND
tabRcptAddr.uidAddr=tabEmailAddress.uid AND
tabEmailHeaders.uid=tabRcptAddr.uidEmail  AND
tabStore.uid=tabEmailHeaders.uidStore AND tabEmailHeaders.uidStore=3 AND
tabEmailHeaders.nFlags=2  GROUP BY tabEmailHeaders.uid LIMIT 20

3.7.3 FAST (~100ms)
0|2|TABLE tabEmailHeaders WITH INDEX indtabEmailHeadersnFlags ORDER BY
1|3|TABLE tabStore USING PRIMARY KEY
2|1|TABLE tabRcptAddr WITH INDEX indtabRcptAddruidEmail
3|0|TABLE tabEmailAddress USING PRIMARY KEY

3.7.8 SLOW  (~ minutes)
0|0|2|SEARCH TABLE tabEmailHeaders USING INDEX indtabEmailHeadersnFlags
(nFlags=
?) (~2 rows)
0|1|3|SEARCH TABLE tabStore USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|0|SEARCH TABLE tabEmailAddress USING COVERING INDEX
indtabEmailAddressszAddr
ess (szAddress=?) (~40 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|3|1|SEARCH TABLE tabRcptAddr USING INDEX indtabRcptAddruidAddr (uidAddr=?)
(~2
-

EXPLAIN QUERY PLAN SELECT tabEmailHeaders.* from
tabRcptAddr,tabEmailHeaders,tabStore WHERE tabRcptAddr.uidAddr IN (SELECT
tabEmailAddress.uid from tabEmailAddress where
tabEmailAddress.szAddress='a...@b.com' OR tabEmailAddress.szAddress='c...@b.com'
OR tabEmailAddress.szAddress='d...@b.com' OR
tabEmailAddress.szAddress='e...@b.com') AND
tabEmailHeaders.uid=tabRcptAddr.uidEmail  AND
tabStore.uid=tabEmailHeaders.uidStore AND tabEmailHeaders.uidStore=3 AND
tabEmailHeaders.nFlags=2  GROUP BY tabEmailHeaders.uid LIMIT 20

3.7.3 FAST (~100ms)
0|1|TABLE tabEmailHeaders WITH INDEX indtabEmailHeadersnFlags ORDER BY
1|2|TABLE tabStore USING PRIMARY KEY
2|0|TABLE tabRcptAddr WITH INDEX indtabRcptAddruidEmail
0|0|TABLE tabEmailAddress WITH INDEX indtabEmailAddressszAddress

3.7.8 FAST (~100ms)
0|0|1|SEARCH TABLE tabEmailHeaders USING INDEX indtabEmailHeadersnFlags
(nFlags=
?) (~2 rows)
0|1|2|SEARCH TABLE tabStore USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|2|0|SEARCH TABLE tabRcptAddr USING INDEX indtabRcptAddruidEmail
(uidEmail=?) (
~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE tabEmailAddress USING COVERING INDEX
indtabEmailAddressszAddr
ess (szAddress=?) (~40 rows)
1|0|0|EXECUTE LIST SUBQUERY 2
-

.indices
indtabEmailAddressnRefs
indtabEmailAddressszAddress
indtabEmailHeaderslnDate
indtabEmailHeaderslnDateChanged
indtabEmailHeadersnFlags
indtabEmailHeadersuidFrom
indtabEmailHeadersuidSndr
indtabEmailHeadersuidStore
indtabRcptAddruidAddr
indtabRcptAddruidEmail

I have tried with both Automatic index on and off. There is 128MB of cache
and the DB is less than 128MB in size.

Any help much appreciated. Sorry for my poor SQL if that is the issue! 
-- 
View this message in context: 
http://old.nabble.com/3.7.3--%3E-3.7.8-changes-tp32846314p32846314.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Difference between FUNCTION(X)>='Y' and FUNCTION(X)>=FUNCTION('Y')

[Soundfaction]

I have a situation where I get different results for this:

select ... where FUNCTION(X)>='Y' 

vs

select ... where FUNCTION(X)>=FUNCTION('Y')

And FUNCTION('Y')  == 'Y' so I thought there should be no difference between
the two queries.

For some reason in the latter case it behaves as:

select ... where FUNCTION(x)>'Y'

This is SQLITE version 3.7.3 as we are still having performance issues with
3.7.9

In this specific case FUNCTION(x) just skips some characters at the start of
the string X and returns an alloc'ed string of the rest. e.g

FUNCTION('RE: this') produces 'this'
-- 
View this message in context: 
http://old.nabble.com/Difference-between-FUNCTION%28X%29%3E%3D%27Y%27-and-FUNCTION%28X%29%3E%3DFUNCTION%28%27Y%27%29-tp32875942p32875942.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between FUNCTION(X)>='Y' and FUNCTION(X)>=FUNCTION('Y')

[Soundfaction]

Thanks for the tip for doing hex() on the results.

My function wasn't returning what I expected due to sqlite3_result_text16
taking bytes not characters. 

it now works fine.



Soundfaction wrote:
> 
> I have a situation where I get different results for this:
> 
> select ... where FUNCTION(X)>='Y' 
> 
> vs
> 
> select ... where FUNCTION(X)>=FUNCTION('Y')
> 
> And FUNCTION('Y')  == 'Y' so I thought there should be no difference
> between the two queries.
> 
> For some reason in the latter case it behaves as:
> 
> select ... where FUNCTION(x)>'Y'
> 
> This is SQLITE version 3.7.3 as we are still having performance issues
> with 3.7.9
> 
> In this specific case FUNCTION(x) just skips some characters at the start
> of the string X and returns an alloc'ed string of the rest. e.g
> 
> FUNCTION('RE: this') produces 'this'
> 

-- 
View this message in context: 
http://old.nabble.com/Difference-between-FUNCTION%28X%29%3E%3D%27Y%27-and-FUNCTION%28X%29%3E%3DFUNCTION%28%27Y%27%29-tp32875942p32876040.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users