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

​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

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

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); > >

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

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

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

>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

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

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

Re: [sqlite] Column name as a variable

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

Re: [sqlite] Column name as a variable

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,

Re: [sqlite] Column name as a variable

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

Re: [sqlite] Column name as a variable

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

Re: [sqlite] Column name as a variable

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(*)

[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: