Re: [sqlite] Subselects negating benefit of index?

[Igor Tandetnik]

"Damien Elmes"  wrote
in message
news:625e49d20904042027y7c029d0fu2cc3812143225...@mail.gmail.com
> I have the following query:
>
>> explain query plan select id from cards where id in (select cardId
>> from cardTags where cardTags.tagId in (246)) order by cards.question
>> collate nocase
> order   fromdetail
> 0   0   TABLE cards USING PRIMARY KEY
> 0   0   TABLE cardTags WITH INDEX ix_cardTags_tagCard
>
> It runs rather slowly. There is an index for the order:
>
> CREATE INDEX ix_cards_sort on cards (question collate nocase);

SQLite can only use one index per table. Given a choice between using 
one on cards.id to satisfy the WHERE clause and one on cards(question) 
to satisfy ORDER BY, it chose the former. This could be a poor choice if 
the WHERE clause selects a significant percentage of all records. To 
suppress the use of this index, write "... where +id in ...".

> So it seems that the subselect is preventing the ordering index from
> being used. What's interesting is that if I select the ids in a
> different sql statement, concatenate them together in a big list of
> numbers, and supply that in the extra statement,  the ordering index
> is still not used, but the query runs twice as fast!

Is it still faster if you include the time of running that separate 
statement for selecting IDs?
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

[sqlite] Subselects negating benefit of index?

[Damien Elmes]

Hi folks,

I have the following query:

> explain query plan select id from cards where id in (select cardId from 
> cardTags where cardTags.tagId in (246)) order by cards.question collate nocase
order   fromdetail
0   0   TABLE cards USING PRIMARY KEY
0   0   TABLE cardTags WITH INDEX ix_cardTags_tagCard

It runs rather slowly. There is an index for the order:

CREATE INDEX ix_cards_sort on cards (question collate nocase);

And if the sql statement is changed to the following, it runs fast:

> explain query plan select id from cards order by cards.question collate nocase
order   fromdetail
0   0   TABLE cards WITH INDEX ix_cards_sort ORDER BY

So it seems that the subselect is preventing the ordering index from
being used. What's interesting is that if I select the ids in a
different sql statement, concatenate them together in a big list of
numbers, and supply that in the extra statement,  the ordering index
is still not used, but the query runs twice as fast!

Unfortunately breaking the subselect into a join is not an option as
the subselect may make use of intersect and except clauses as it gets
more complex. Would sqlite be able to handle this situation any better
than it does at the moment?

Cheers,

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

Re: [sqlite] Insert mode shows wrong type

[Jay A. Kreibich]

On Sat, Apr 04, 2009 at 05:25:38PM +1100, BareFeet scratched on the wall:
> Hi Jay,
> 
> Thanks for the reply.
> 
> > Asking SQLite for the insert statement doesn't tell you what the  
> > type in the database is
> 
> Shouldn't it?

  In my opinion, no.  For one thing, depending on the format of a literal
  representation is a very poor indicator of type.  Raw SQL is not
  meant to express type-- traditionally that is what the tables are
  for.  The only way SQLite gets away with it is that SQLite has relatively
  few storage classes.

  But if you've got date types, time types, timestamps, monetary types,
  different string types, etc., etc., like most "real" RDBMS engines, 
  there is an assumption that nearly any value expressed as a literal
  (usually a string) will be modified for storage.
  
> It doesn't seem that any output mode in the sqlite3  
> command will distinguish types.

  Perhaps, although that's no different than any other database I've
  worked with.  The API most definitely does provide all the type
  information you need, and that's what really counts.

  If you really care about what the type is, you can just ask.

> CSV sometimes quotes text, sometimes doesn't.

  CSV is a horrid format.  Despite what everyone seems to think, CSV is
  about as standardized as a pile of rocks.  It works well enough,
  until you add commas or new-lines to values.  Then you need to
  quote.  Then you need to escape a quote.  Then you need to escape and
  escape.  Pretty soon everyone has a different idea of the "right" way
  to do CSV.

  And even if you can agree on a CSV format, it was never meant to
  convey any kind of type information.

> And now it seems insert doesn't quote text and not numbers.

  Yeah, and in this case I would agree that this seems broken.

> > If the column is declared to have a TEXT affinity, any numbers will  
> > be converted to text strings and stored as text.  That's true of  
> > your INSERT statements as well as the automatically generated  
> > statements.  The end result in both cases is numbers being stored as  
> > strings.
> 
> OK, let's change the test to not specify affinity for the column:

> > the code is smart enough to know that it can coerce the number-as- 
> > text to a number, since it will be coerced back into a text value  
> > upon INSERT.
> 
> In this case, that logic doesn't seem to hold. Reinjecting the  
> generated 456 insert statement above would insert an integer instead  
> of the original text. So the SQLite produced insert statement is  
> "wrong" in the sense that running it would give a different value  
> (type) than the original.

  Yeah.  Apparently the code is not smart enough, it was just lucky
  enough the first time.

  Here's an interesting point, however... "dump" does the right thing.

$ sqlite3
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> create table t ( c );
sqlite> insert into t values ( 123 );
sqlite> insert into t values ('456' );
sqlite> insert into t values ('789z' );
sqlite> .dump t
BEGIN TRANSACTION;
CREATE TABLE t ( c );
INSERT INTO "t" VALUES(123);
INSERT INTO "t" VALUES('456');
INSERT INTO "t" VALUES('789z');
COMMIT;
sqlite> .mode insert
sqlite> select * from t;
INSERT INTO table VALUES(123);
INSERT INTO table VALUES(456);
INSERT INTO table VALUES('789z');

  Which brings us back to some interesting ground.  If dump did it
  incorrectly, I'd clearly say that's a bug.  But if dump does it
  right, and this is limited to just the mode insert, things are less
  clear.  Most output modes do not mark or preserve type information.
  For example, the default mode list does not mark strings as such, the
  value of the rows are simply displayed.  From that standpoint, I can
  see an both sides of the argument as to if this behavior is
  "correct" or not.  Given the way all the other normal output modes
  work, I'd say there is a strong case it is correct.
  
> As I mentioned, there therefore doesn't appear to be any way to get  
> the command line to show the data in a table with implied type.

  Not as an output format, but as I did in my last post, you can simply
  ask SQLite to display the types in a different output column:

select MyData, typeof(MyData) from MyTable;

  That will produce a two column output: first column with values,
  second column with types.  The API, as well, provides hard and fast
  type information.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multiple row insert

[Igor Tandetnik]

"Justin Lolofie"  wrote
in message
news:bb1c266a0904041639s277ce09cpe960a09e94c4b...@mail.gmail.com
> Is there syntax to do multiple row insert? Something like:
>
> insert into my_table (a,b,c) values ((1,2,3),(4,5,6));
>
> The documentation for INSERT seems to imply this is not possible.

Well, you could do something like this:

insert into my_table(a, b, c) select * from
(select 1, 2, 3
 union all
 select 4, 5, 6);

Though it's not clear why you can't just issue a plain vanilla INSERT 
VALUES statement once for each row.

Igor Tandetnik 



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

[sqlite] multiple row insert

[Justin Lolofie]

Is there syntax to do multiple row insert? Something like:

insert into my_table (a,b,c) values ((1,2,3),(4,5,6));

The documentation for INSERT seems to imply this is not possible.

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

Re: [sqlite] Insert mode shows wrong type

[BareFeet]

Hi Jay,

Thanks for the reply.

> Asking SQLite for the insert statement doesn't tell you what the  
> type in the database is

Shouldn't it? It doesn't seem that any output mode in the sqlite3  
command will distinguish types. CSV sometimes quotes text, sometimes  
doesn't. And now it seems insert doesn't quote text and not numbers.

> If the column is declared to have a TEXT affinity, any numbers will  
> be converted to text strings and stored as text.  That's true of  
> your INSERT statements as well as the automatically generated  
> statements.  The end result in both cases is numbers being stored as  
> strings.

OK, let's change the test to not specify affinity for the column:

.mode insert
create temp table "Test" (MyString);
insert into Test values (123);
insert into Test values ('456');
insert into Test values ('789x');
select MyString from Test;

which should give:

insert into Test values (123);
insert into Test values ('456');
insert into Test values ('789x');

but instead gives:

INSERT INTO table VALUES(123);
INSERT INTO table VALUES(456);
INSERT INTO table VALUES('789x');

> the code is smart enough to know that it can coerce the number-as- 
> text to a number, since it will be coerced back into a text value  
> upon INSERT.

In this case, that logic doesn't seem to hold. Reinjecting the  
generated 456 insert statement above would insert an integer instead  
of the original text. So the SQLite produced insert statement is  
"wrong" in the sense that running it would give a different value  
(type) than the original.

As I mentioned, there therefore doesn't appear to be any way to get  
the command line to show the data in a table with implied type.

Thanks,
Tom
BareFeet

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