Re: [sqlite] Why is a b-tree sort required for this query?

[John Hascall]

​If you do
   ORDER BY t1c.t1_id, t1c.id;

then you won't have the b-tree step, but ​including the name fields means
it has the extra work to do to satisfy your order by.  Or am I missing
something?

John



On Sun, Nov 16, 2014 at 1:18 PM, Oliver Smith  wrote:

> In the following scenario:
>
>CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
>CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
>CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
>
>CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
>CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE
>(t2_id, name));
>
> I have a query designed to generate a row for t2c ordered by t2 for every
> instance of t1c ordered by t1 id and then t1c id.
>
> The query uses indexes and those should ensure that the results are in the
> order I am specifying:
>
>EXPLAIN QUERY PLAN
>SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
>FROM t1c,
>  t2 INNER JOIN t2c ON (t2c.t2_id = t2.id)
>ORDER BY t1c.t1_id, t1c.id, t2.name, t2c.name
>;
>
> And yet the plan invokes a B-Tree to sort:
>
>"0""0""0""SCAN TABLE t1c USING COVERING INDEX
>idx_t1c_by_t1_id"
>"0""1""2""SCAN TABLE t2c"
>"0""2""1""SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX
>(id=?)"
>"0""0""0""USE TEMP B-TREE FOR RIGHT PART OF ORDER BY"
>
> Is the temp b-tree redundant here?
>
> $ sqlite3 --version
> 3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
>
> ___
> 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

Re: [sqlite] Why is a b-tree sort required for this query?

[Richard Hipp]

On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith  wrote:

> In the following scenario:
>
>CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
>CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
>CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);
>
>CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
>CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE
>(t2_id, name));
>
> I have a query designed to generate a row for t2c ordered by t2 for every
> instance of t1c ordered by t1 id and then t1c id.
>
> The query uses indexes and those should ensure that the results are in the
> order I am specifying:
>
>EXPLAIN QUERY PLAN
>SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
>FROM t1c,
>  t2 INNER JOIN t2c ON (t2c.t2_id = t2.id)
>ORDER BY t1c.t1_id, t1c.id, t2.name, t2c.name
>;
>
> And yet the plan invokes a B-Tree to sort:
>
>"0""0""0""SCAN TABLE t1c USING COVERING INDEX
>idx_t1c_by_t1_id"
>"0""1""2""SCAN TABLE t2c"
>"0""2""1""SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX
>(id=?)"
>"0""0""0""USE TEMP B-TREE FOR RIGHT PART OF ORDER BY"
>
> Is the temp b-tree redundant here?
>

I don't think so.  What query plan are you thinking might be able to omit
the sorting pass in this query?


>
> $ sqlite3 --version
> 3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Slavin]

On 17 Nov 2014, at 12:48pm, RP McMurphy  wrote:

> Upon further analysis it appears that the data "shape" is different in 
> different periods within the table. That is, some sections have the inverse 
> shape to other sections. So it looked like query times would change over time 
> but actually they are changing because different portions of the table are 
> being accessed.

It would be difficult to spot that.  Nice analysis.

> Is it possible to tell sqlite to analyze different sections and keep separate 
> a record for each?

No, but the amount by which such a wrong choice should increase your execution 
time should be extremely slow.  Are you actually getting annoyingly long 
execution times or are you just trying to wring every last millisecond out of 
your application ?  If the latter, then I feel you should stop worrying about 
this and move onto something else.

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RP McMurphy]

>When you say the data changes, does the shape actually change? Because all that
>matters to analyze is the shape of the data, not the data itself.

Upon further analysis it appears that the data "shape" is different in 
different periods within the table. That is, some sections have the inverse 
shape to other sections. So it looked like query times would change over time 
but actually they are changing because different portions of the table are 
being accessed. Is it possible to tell sqlite to analyze different sections and 
keep separate a record for each?

>Unlike me, some other people on here are extremely knowledgeable on SQL and 
>specifically the inner
>workings of SQLite - Tell us the full story, we might save you hundreds of 
>hours.

I will try to take advantage of that. Let me see what I am able to say about 
our system and hopefully come up with some concise and pertinent questions.

RP

PS: Some administravia; Does anyone know of a way to reduce the posting delay 
for this list? Is it always like this? Or is it some problem with the gmane 
site in general?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why is a b-tree sort required for this query?

[Oliver Smith]

In the following scenario:

   CREATE TABLE t1 (id INTEGER, name text, UNIQUE (name));
   CREATE TABLE t1c (id INTEGER, name text, t1_id INTEGER, UNIQUE (name));
   CREATE INDEX idx_t1c_by_t1_id ON t1c (t1_id, id);

   CREATE TABLE t2 (id INTEGER, name text, UNIQUE(name));
   CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE
   (t2_id, name));

I have a query designed to generate a row for t2c ordered by t2 for 
every instance of t1c ordered by t1 id and then t1c id.


The query uses indexes and those should ensure that the results are in 
the order I am specifying:


   EXPLAIN QUERY PLAN
   SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id
   FROM t1c,
 t2 INNER JOIN t2c ON (t2c.t2_id = t2.id)
   ORDER BY t1c.t1_id, t1c.id, t2.name, t2c.name
   ;

And yet the plan invokes a B-Tree to sort:

   "0""0""0""SCAN TABLE t1c USING COVERING INDEX
   idx_t1c_by_t1_id"
   "0""1""2""SCAN TABLE t2c"
   "0""2""1""SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX
   (id=?)"
   "0""0""0""USE TEMP B-TREE FOR RIGHT PART OF ORDER BY"

Is the temp b-tree redundant here?

$ sqlite3 --version
3.8.5 2014-06-04 14:06:34 b1ed4f2a34ba66c29b130f8d13e9092758019212

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

Re: [sqlite] Column name as a variable

[Keith Medcalf]

With the eval() function loaded,

sqlite> select tbl_name, eval('select count(*) from ' || tbl_name) from 
sqlite_master where type='table';
advisory|10
advlink|67528
crew|144809
crewlink|1710151
genre|201
genrlink|703470
lineup|4
map|646
program|447534
role|14
schedule|162272
station|493
mySeries|310
myChannels|262
sqlite_stat1|32
sqlite_stat4|1481
myTitles|27203

The eval function is found in ext/misc/eval.c of the source distribution (not 
the amalgamation) in file eval.c

http://www.sqlite.org/src/info/27cf665b957f2c0ced403e3032099e80c295598f
http://www.sqlite.org/src/artifact/04e630bde869aa1fec6b993d40591f963be2f868


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Paul Sanderson
>Sent: Monday, 17 November, 2014 03:55
>To: General Discussion of SQLite Database
>Subject: [sqlite] Column name as a variable
>
>Is it possible to get a row count for each of the tables in a database
>using a SQL query.
>
>i.e.
>
>is there a way I could use each row in sqlite_master and use
>table_name to somehow do a select count(*) from
>sqlite.master.table_name
>
>Thanks
>
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
>Toolkit
>-Forensic Toolkit for SQLite
>http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>processing made easy
>___
>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

Re: [sqlite] Column name as a variable

[James K. Lowden]

On Mon, 17 Nov 2014 12:00:06 +
Hick Gunter  wrote:

> SELECT table_name FROM sqlite_master;
> 
> And then, in your programming language of choice, execute

Or, with some determination, you can do it in two steps in pure SQL:
Use SQL to produce SQL, and execute the result, 

SELECT'select count(*), '
|| table_name
|| ' from '
|| table_name
|| ' union '
FROM sqlite_master;

To replace the last 'union clause' in the result with a semicolon, you
could sling a string in the application, or use a correlated subquery
(and ORDER BY) to supply ';' when e.g. table_name  is max(table_name).  

To do it in one fell swoop in SQL, you need a virtual table that will
execute SQL for you.  Supply the above as input, and get two columns of
output.  

--jkl

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

Re: [sqlite] Column name as a variable

[Hick Gunter]

SELECT table_name FROM sqlite_master;

And then, in your programming language of choice, execute

SELECT count() FROM 

For each received table name.

You cannot use a variable instead of a table name in SQL.

-Ursprüngliche Nachricht-
Von: Paul Sanderson [mailto:sandersonforens...@gmail.com]
Gesendet: Montag, 17. November 2014 11:55
An: General Discussion of SQLite Database
Betreff: [sqlite] Column name as a variable

Is it possible to get a row count for each of the tables in a database using a 
SQL query.

i.e.

is there a way I could use each row in sqlite_master and use table_name to 
somehow do a select count(*) from sqlite.master.table_name

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC 
processing made easy ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Column name as a variable

[Paul Sanderson]

Thanks Simon

I suspected as much - UNION is no good for me, it's easy enough to
iterrate through in C. But not really what I was after.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 17 November 2014 11:38, Simon Slavin  wrote:
>
> On 17 Nov 2014, at 10:55am, Paul Sanderson  
> wrote:
>
>> Is it possible to get a row count for each of the tables in a database
>> using a SQL query.
>>
>> i.e.
>>
>> is there a way I could use each row in sqlite_master and use
>> table_name to somehow do a select count(*) from
>> sqlite.master.table_name
>
> No, but you can use UNION to get something like it:
>
> SELECT 'table1',count(*) FROM table1
> UNION
> SELECT 'table2',count(*) FROM table2
>
> should do something like what you want.  As far as I know, there's no way 
> within SQL to use a table name as a variable.  I suspect that this was done 
> deliberately to enforce correct schema.
>
> Simon.
> ___
> 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

Re: [sqlite] Column name as a variable

[Simon Slavin]

On 17 Nov 2014, at 10:55am, Paul Sanderson  wrote:

> Is it possible to get a row count for each of the tables in a database
> using a SQL query.
> 
> i.e.
> 
> is there a way I could use each row in sqlite_master and use
> table_name to somehow do a select count(*) from
> sqlite.master.table_name

No, but you can use UNION to get something like it:

SELECT 'table1',count(*) FROM table1
UNION
SELECT 'table2',count(*) FROM table2

should do something like what you want.  As far as I know, there's no way 
within SQL to use a table name as a variable.  I suspect that this was done 
deliberately to enforce correct schema.

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

[sqlite] Column name as a variable

[Paul Sanderson]

Is it possible to get a row count for each of the tables in a database
using a SQL query.

i.e.

is there a way I could use each row in sqlite_master and use
table_name to somehow do a select count(*) from
sqlite.master.table_name

Thanks


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users