Hi there,
I'm wondering how larger BLOBs in a database affect performance of accessing
the non-blob data. We've got a database with potentially a few million
records in the main tables (of just strings and ints), but joined to that data
set we want to store up to thousands (maybe 75000 max)
On Mon, Mar 01, 2010 at 11:44:51PM +0100, Jean-Christophe Deschamps scratched
on the wall:
>
>> So indexes are not used for NOT conditions, as NOT conditions
>> generally require a full scan, regardless. Yes, it is a simple
>> reverse of a binary test, but the reverse of a specific indexed
>I haven't been able to think of how it would preclude using the index,
>but I suspect it's more a matter of needing a similar-but-different
>codepath to optimize for the NOT case, rather than a simple "invert
>this" codepath relying on the existing case. Which is really just
>another way of stat
Hi all,
I've been playing around with the FTS3 (via the amalgamation src) on a
mobile device and it's working well. But my db file size is getting
pretty big and I was looking for a way to compress it. I've seen some
earlier posts from Alexey for his compression modifications to the
FTS3 extension
On Mon, Mar 1, 2010 at 3:12 PM, Jean-Christophe Deschamps
wrote:
>>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT
>>(col = 12345).
>
> You're right of course! (and I was even saying about nulls treated apart)
>
> But, in your view, that the set can be non-contiguous for
>
>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT
>(col = 12345).
You're right of course! (and I was even saying about nulls treated apart)
But, in your view, that the set can be non-contiguous for
negative/negated conditions would it explain that current code can't
make
On Mon, Mar 1, 2010 at 2:44 PM, Jean-Christophe Deschamps
wrote:
> The actual reason for the way NOT works as for now may be due to the
> fact that negating a condition may cause the resulting set to be in
> fact itself the union of two subsets.
> Say the "where" condition K is "col = 12345". We
> So indexes are not used for NOT conditions, as NOT conditions
> generally require a full scan, regardless. Yes, it is a simple
> reverse of a binary test, but the reverse of a specific indexed
> lookup of a known value is a table scan to gather all the unknown
> values.
Jay,
I under
Hello!
On Monday 01 March 2010 14:48:46 Dan Kennedy wrote:
>
> On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote:
>
> >> It would seem to me that asking [1='1'] *should* return false
> >> because the
> >> numeric and character string value domains are logically disjoint,
> >> so no value
>
What happens if you change double quote marks to single
quote marks?
On Mon, 2010-03-01 at 08:52 -0800, Ray Irvine wrote:
> > I did not get an e-mail response and the status check URL has expired.
> > Thank you
> >
> > I have found the following:
> >
> > BEGIN; UPDATE entry SET Spinnaker="No"
On Mon, Mar 01, 2010 at 02:06:05PM -0600, Jay A. Kreibich scratched on the wall:
> Also, it is highly unlikely the expression "parent == NULL" does what
> you want, given that it will *always* return NULL. Try "parent IS
> NOT NULL".
Er... I mean "parent IS NULL".
-j
--
Jay A. Kre
On Mon, Mar 01, 2010 at 06:47:41PM +0300, ?? ??
scratched on the wall:
> Hello!
>
> I need to check if parent of group is exist or not.
> Parent stored in "parent" column and may be NULL or will be existing
> "idgroup" column value.
> It does not works because subqueries
Did you consider using foreign keys (assuming you have the latest
version of SQLite)?
Pavel
On Mon, Mar 1, 2010 at 10:47 AM, Артемий Васюков wrote:
> Hello!
>
> I need to check if parent of group is exist or not.
> Parent stored in "parent" column and may be NULL or will be existing
> "idgroup"
I have been experiencing a strange issue with savepoint error for the
library version 3.6.22.
I have the following code:
char* sql = "SAVEPOINT sp;";
char* err;
slim_int ret = sqlite3_exec(sqlitehandle, sql, NULL, NULL, &err);
The ret is 1 which is SQLITE_ERROR
and err = "near \"sp\": syntax error
> I did not get an e-mail response and the status check URL has expired. Thank
> you
>
> I have found the following:
>
> BEGIN; UPDATE entry SET Spinnaker="No" ,Singlehand="Doublehand"
> ,Doublehand="Third" ,Multi="Multihull" ,Furler=" " WHERE ROWID="45"; COMMIT
>
> Results in the value of t
Hello!
I need to check if parent of group is exist or not.
Parent stored in "parent" column and may be NULL or will be existing
"idgroup" column value.
It does not works because subqueries prohibited in CHECK constraints.
My table showed below (incorrect constraint commented).
--
On Mon, Mar 01, 2010 at 06:03:49PM +0100, Jean-Christophe Deschamps scratched
on the wall:
>
> >I totally disagree with you. Let's say you have 1,000,000 rows and 100
> >of them contain NULL. In this situation selecting NOT NULL will select
> >almost all rows which means that using index in this
>maybe NOT is implemented the same way as any other
>function and so it cannot be optimized using index.
That's possible, but other logical operators don't exhibit the same
bahavior and will not prevent the use of indexes. That NOT is not
being handled at the same _logical_ level than AND and
> The point was that NOT is
> simply reversing the issue of a binary test, in fine, and that seems
> essentially independant of the use of an index for determining .
I agree with the point that NOT prevents using an index for some
reason. I'm not sure but probably that's because of the same issue
>> The following sql is supposedly valid:
>>
>> select 1 in (1)'hello';
>
> Apparently this gets interpreted as
>
> select (1 in (1)) as "hello";
>
>("as" is optional), under SQLite's long-standing policy of
> accepting single and double quotes more or less
> interchangeably.
Hmm, I'd not have g
>I totally disagree with you. Let's say you have 1,000,000 rows and 100
>of them contain NULL. In this situation selecting NOT NULL will select
>almost all rows which means that using index in this case doesn't give
>any performance boost. So here using full scan for NOT NULL condition
>is better
> Anyway, it seems the OP has a point in saying that it would be nice
> --and I would say 'natural'-- to have the optimizer enhanced to handle
> "NOT " as efficiently as it handles ", provided
> such enhancement can be done with only little changes.
I totally disagree with you. Let's say you have
> > "SELECT count(*) WHERE NOT text IS NULL"
> >
> > requires that the complete text column is loaded. With a stored LOB
> > this results in crazy performance.
>
>How did you find that? What do you mean by "requires loading of the
>whole text column"? It pretty much can require even loading of tex
> "SELECT count(*) WHERE NOT text IS NULL"
>
> requires that the complete text column is loaded. With a stored LOB
> this results in crazy performance.
How did you find that? What do you mean by "requires loading of the
whole text column"? It pretty much can require even loading of text
columns th
sqlite3_changes() is exactly what you should use in this case. And I
didn't understand why did you find it unsuitable for you?
Pavel
On Mon, Mar 1, 2010 at 8:22 AM, yangchen wrote:
> HI,
>
> How could i get the rows affected by current sql statement ?
>
> "sqlite3_changes" can only get the rows
HI,
How could i get the rows affected by current sql statement ?
"sqlite3_changes" can only get the rows affected by the most recently sql
statement which really affect rows.
But i don't know whether current sql statement can affect rows.
For example:
1) First ,i open the database connection.
Regarding:
BTW, can you recommend me suitable UI to work with SQLite?
Maybe you've already tried out some of the GUI tools listed at:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
I'm not sure what your requirements and preferences are, or what operating
system you're using, or w
wcl...@gfs-hofheim.de wrote:
> The following sql is supposedly valid:
>
> select 1 in (1)'hello';
Apparently this gets interpreted as
select (1 in (1)) as "hello";
("as" is optional), under SQLite's long-standing policy of accepting single and
double quotes more or less interchangeably.
--
Ig
On Mar 1, 2010, at 6:08 PM, Alexey Pechnikov wrote:
>> It would seem to me that asking [1='1'] *should* return false
>> because the
>> numeric and character string value domains are logically disjoint,
>> so no value
>> from one could ever equal a value from another, and so SQLite's
>> answ
Hello!
On Monday 01 March 2010 08:05:06 Darren Duncan wrote:
> Alexey Pechnikov wrote:
> > Hello!
> >
> > On Thursday 25 February 2010 02:53:32 Igor Tandetnik wrote:
> >> http://www.sqlite.org/datatype3.html
> >> For conversions between TEXT and REAL storage classes, SQLite considers
> >> the co
Hi,
I spotted this while looking into something else for Alexey:
The following sql is supposedly valid:
select 1 in (1)'hello';
where as
select * from t where 1 in (1)'hello';
returns "Error: near "'hello'": syntax error" (which I would expect!)
This occurs on v3.6.22, but I haven't checked
> Now, it is probable that the core can be patched as you request by
> assigning affinity to constants during parsing, but I haven't had an
> opportunity to look into it and I would have to check very
> thoroughly that it will not brake something else if I do patch it
> for you. At this precise
Alexey,
I can certainly tell you what is causing this behaviour:
When sqlite does a comparison, it checks the *column* affinity from both
sides. In your example, neither sides come from a column, but are
constants. Therefore, sqlite uses the NONE affinity on comparison (I
think, but I haven'
33 matches
Mail list logo