Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-15 Thread Jean-Baptiste Gardette
Thank you Keith for the detail explanation. I misunderstood the 2 replies were opposite but this is not the case. Thank you again Jean-bapstiste ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-15 Thread Keith Medcalf
On Wednesday, 15 January, 2020 02:06, Jean-Baptiste Gardette wrote: > Just to be sure, is it unsafe to write a non agregate SELECT with GROUP > BY and HAVING clauses (without sub-SELECT) for the sole prupose > explained before (even if the approache is discutable) ? Presently, yes it is. >I

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-15 Thread Jean-Baptiste Gardette
Just to be sure, is it unsafe to write a non agregate SELECT with GROUP BY and HAVING clauses (without sub-SELECT) for the sole prupose explained before (even if the approache is discutable) ? I understand 2 different answers here : - "No, this kind of query can't be rewritten by the

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Richard Hipp
On 1/14/20, Keith Medcalf wrote: > > I seem to recall something about "expensive" conditions that will be forced > to be run on only as few surviving candidate rows as possible, but my > recollection is vague (they say the memory is the second thing to go -- > strange I can't remember the first).

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 09:23, Simon Slavin wrote: >Would it be possible to phrase your SELECT as a SELECT with a sub-SELECT >? Have the sub-SELECT figure out which rows you want in which order, >then use a SELECT to apply your UDF to them ? It is guaranteed that the >sub-SELECT is

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Simon Slavin
On 14 Jan 2020, at 4:14pm, Jean-Baptiste Gardette wrote: > The reason i asked this is that i have a query in wich one condition > filtering the recordset involves > an UDF and this UDF needs to be processed after all table filters have been > applied You cannot guarantee this. And even if

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Jean-Baptiste Gardette
Thank you Dominic and Keith for your replies The reason i asked this is that i have a query in wich one condition filtering the recordset involves an UDF and this UDF needs to be processed after all table filters have been applied Illustration : additionnal table : CREATE TABLE t2 ( a TEXT

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Keith Medcalf
On Tuesday, 14 January, 2020 06:58, Jean-Baptiste Gardette wrote: >Consider the following exemple : >CREATE TABLE t1 ( >a TEXT PRIMARY KEY, >b INTEGER); >SELECT * >FROM t1 >GROUP BY a >HAVING b > 1; >Will the GROUP BY clause be supressed and HAVING clause be rewritten in >WHERE clause by

Re: [sqlite] Query Planner GROUP BY and HAVING clauses optimization ?

2020-01-14 Thread Dominique Devienne
On Tue, Jan 14, 2020 at 2:57 PM Jean-Baptiste Gardette wrote: > SELECT * FROM t1 GROUP BY a HAVING b > 1; > > Will the GROUP BY clause be supressed and HAVING clause be rewritten in WHERE > clause by the optimizer ? My question would be why you wouldn't write it as a WHERE clause in the first

Re: [sqlite] Query regarding Sqlite 3.30.1

2019-12-19 Thread Richard Hipp
On 12/19/19, Uthra Ganesan wrote: > Hi > > We are currently using SQLite 3.28.0. Can you please lt us know when can we > expect 3.30.1 in Maven central repository. > Please ask whomever controls the "Maven central repository". They will make that determination, not us. -- D. Richard Hipp

Re: [sqlite] Query freezing on latest SQLite 3.30.1 build

2019-12-05 Thread David Raymond
The mailing list strips off all attachments, so you'll have to provide another place to get that. Alternatively could you post the schema here as text, along with the explain query plan output from the slow version and from a fast version? -Original Message- From: sqlite-users On

Re: [sqlite] Query for Many to Many

2019-09-06 Thread Simon Slavin
On 6 Sep 2019, at 7:36am, Rowan Worth wrote: > I was surprised when this behaved differently in other SQL engines. eg. in > SQLite you can write: > > SELECT col1, col2 FROM table1, table2 USING But please don't, for the reason you gave. Not only is it ambiguous but different SQL engines

Re: [sqlite] Query for Many to Many

2019-09-06 Thread Rowan Worth
On Tue, 3 Sep 2019 at 22:17, Keith Medcalf wrote: > And the "," in the list of tables may be replaced by the word JOIN. It is > merely an alternate spelling. > I was surprised when this behaved differently in other SQL engines. eg. in SQLite you can write: SELECT col1, col2 FROM table1,

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

2019-09-04 Thread Dan Kennedy
On 2/9/62 16:57, Paul wrote: I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : I have a test case when the regression can be observed in queries that use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. For some reason

Re: [sqlite] Query for Many to Many

2019-09-03 Thread Keith Medcalf
" comments here> >> > USING needs parenthesis around the column list: ...using >> > (author_id)...using (book_isbn)... >> > -Original Message- >> > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On >> > Behalf Of Dominique Devienne &g

Re: [sqlite] Query for Many to Many

2019-09-03 Thread dboland9
author_id)...using (book_isbn)... > > -Original Message- > > From: sqlite-users sqlite-users-boun...@mailinglists.sqlite.org On > > Behalf Of Dominique Devienne > > Sent: Tuesday, August 27, 2019 10:08 AM > > To: SQLite mailing list sqlite-users@mailinglists.sqlite

Re: [sqlite] Query for Many to Many

2019-09-03 Thread John G
; > > USING needs parenthesis around the column list: ...using > (author_id)...using (book_isbn)... > > > -Original Message- > From: sqlite-users On > Behalf Of Dominique Devienne > Sent: Tuesday, August 27, 2019 10:08 AM > To: SQLite mailing list >

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

2019-09-02 Thread Paul
I has been a while without response, so I just bumping this message. 19 July 2019, 14:21:27, by "Paul" : > I have a test case when the regression can be observed in queries that > use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0. > For some reason the planner decides to

Re: [sqlite] Query for Many to Many

2019-08-27 Thread David Raymond
It does support natural joins. USING needs parenthesis around the column list: ...using (author_id)...using (book_isbn)... -Original Message- From: sqlite-users On Behalf Of Dominique Devienne Sent: Tuesday, August 27, 2019 10:08 AM To: SQLite mailing list Subject: Re: [sqlite

Re: [sqlite] Query for Many to Many

2019-08-27 Thread Dominique Devienne
On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne wrote: > select author.*, books.* > from author_books > join author on author.author_id = author_books.author_id > join books on books.book_isbn = author_books.book_isbn > Which can also be written: select author.*, books.* from

Re: [sqlite] Query for Many to Many

2019-08-27 Thread Dominique Devienne
On Tue, Aug 27, 2019 at 3:38 PM dboland9 wrote: > I need some help writing some queries for a MTM relationship. The example > tables are: > > author table books table author_books table > author_id PKbook_isbn PKa_b_id PK > author_fnamebook_title

Re: [sqlite] Query for Many to Many

2019-08-27 Thread David Raymond
The basic query is going to be the below select stuff from books inner join author_books on author_books.book_isbn = books.book_isbn inner join author on author_books.author_id = author.author_id where things; -Original Message- From: sqlite-users On Behalf Of dboland9

Re: [sqlite] Query Builder Access

2019-07-21 Thread Simon Slavin
On 20 Jul 2019, at 7:33pm, Revere Perkins wrote: > Is the SQL Query Builder only available in the licensed version? Sorry. SQLite is always completely free, and there's no Query Builder included as part of SQLite. You must be asking about a third-party product. And there are lots of SQL

Re: [sqlite] Query planner: Covering index not chosen over primary key

2019-05-03 Thread Jen Pollock
Thank you for the suggestion! The actual schema & query are a good deal more complicated, and I'm not looking for general optimization help with them right now. Jen Pollock On Fri, May 03, 2019 at 10:11:04PM +0100, Simon Slavin wrote: > On 3 May 2019, at 9:34pm, Jen Pollock wrote: > > >

Re: [sqlite] Query planner: Covering index not chosen over primary key

2019-05-03 Thread Simon Slavin
On 3 May 2019, at 9:34pm, Jen Pollock wrote: > SELECT filename > FROM images >JOIN embedded_files ON images.file_id == embedded_files.id > WHERE type == 'png'; Try this: CREATE INDEX images (type, file_id); ANALYZE; ___ sqlite-users mailing

Re: [sqlite] Query planner: Covering index not chosen over primary key

2019-05-03 Thread Richard Hipp
On 5/3/19, Jen Pollock wrote: > I assume the problem here is that the primary key is usually a weird > thing to index. I can definitely work around this, but I thought it > might be worth reporting as something that could perhaps be improved in > the query planner. Thank you. I have your

Re: [sqlite] Query Regression IN and Virtual Tables - followup

2019-04-05 Thread Dan Kennedy
Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dan Kennedy Gesendet: Freitag, 29. März 2019 14:30 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Query Regression IN and Virtual Tables On 29/3/62 14:32, Hick Gunter wro

Re: [sqlite] Query Regression IN and Virtual Tables

2019-03-29 Thread Dan Kennedy
On 29/3/62 14:32, Hick Gunter wrote: When upgrading from 3.7.14.1 to 3.24 I noticed the following problem Given a virtual table like CREATE VIRTUAL TABLE vt ( key1 INTEGER, key2 INTEGER, key3 INTEGER, attr1 INTEGER,...); whose xBestIndex function simulates (in unsupported syntax) CREATE

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-18 Thread niklas
Thanks to all who have replied, very informative! :) This is just a database for own personal use so it's not a big deal in any way, mainly trying to get a better understanding of how Sqlite works here. I'll note that the sql queries are not static inside my application but they are generated

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread Keith Medcalf
On Sunday, 17 March, 2019 11:19, niklas wrote: >I agree that correlated subqueries in general seem more natural and >are probably also less likely to have the performance pessimizations >noticed with joins. >But I might also want to use the column, or in case of a correlated >subquery, the

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread Simon Slavin
On 17 Mar 2019, at 5:19pm, niklas wrote: > Is this a recent change in Sqlite or have I misunderstood something? The > Sqlite documentation still does not seem to say that column aliases can be > used in the WHERE clause at least. You are correct in two things. Column aliases cannot be relied

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-17 Thread niklas
I agree that correlated subqueries in general seem more natural and are probably also less likely to have the performance pessimizations noticed with joins. But I might also want to use the column, or in case of a correlated subquery, the column alias, in the WHERE clause and previously that has

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-16 Thread Simon Slavin
On 15 Mar 2019, at 7:02pm, niklas wrote: > The data used for sqlite_stat1 in create.txt is taken from the real data, > it's copied from the sql-dump generated just after running ANALYZE. Okay. I should have guessed that. Sorry for doubting you. You seem to have figured out a work-around for

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-16 Thread niklas
The data used for sqlite_stat1 in create.txt is taken from the real data, it's copied from the sql-dump generated just after running ANALYZE. I only wanted to include the minimum amount of data the demonstrate the issue so I omitted all other tables, views and data. As I understand it sqlite only

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-15 Thread Keith Medcalf
nal Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Friday, 15 March, 2019 14:44 >To: SQLite mailing list >Subject: Re: [sqlite] Query planner: Scanning subqueries vs using >automatic covering index > >

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-15 Thread Keith Medcalf
The current tip of trunk produced the same results demonstrated by Niklas in his original post for his original testcase for me. I would have written the query as a simple query with correlated subqueries to get the concatenated data as it is a "more natural" declaration of what is wanted (in

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

2019-03-15 Thread Simon Slavin
On 15 Mar 2019, at 7:35am, niklas wrote: > Dropping analyze (sqlite_stat1) information from the database made > the problem go away, i.e. made sqlite use index instead of scanning. Instead of dropping ANALYZE information, did you try updating it instead, by running ANALYZE again ? Ah ... >

Re: [sqlite] Query Planning Knowledge

2019-01-23 Thread Clemens Ladisch
Andy Bennett wrote: >> foreign key constraints > > my experience with other engines taught me that it makes experimenting at the > monitor harder. Then don't use them. :) But do you actually want 'wrong' data? > Are there any efficiency benefits or is it just there to enforce data >

Re: [sqlite] Query Planning Knowledge

2019-01-23 Thread Andy Bennett
Hi, I could use the inner join for the "entrys" join and the "items" join but not the "entry-items" join because each entry can have more than one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b USING (id);

Re: [sqlite] Query Planning Knowledge

2019-01-22 Thread Clemens Ladisch
Andy Bennett wrote: > I could use the inner join for the "entrys" join and the "items" join > but not the "entry-items" join because each entry can have more than > one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN

Re: [sqlite] Query Planning Knowledge

2019-01-22 Thread Andy Bennett
Hi David, Thanks for your thoughtful reply. Can't go into as much detail as you. But a couple comments. "primary key unique" is redundant, and will actually create a redundant unique index. Are you refering to the CREATE TABLE clauses for the "items" and "registers" tables? I appear

Re: [sqlite] Query Planning Knowledge

2019-01-22 Thread Keith Medcalf
Your description of the schema does not match the schema that you have shown. (As an aside, I wonder why you are using such confusing table and column names that require the puking of quotes all over the place? You can avoid such ugliness by searching and replacing all "-" with nothingness

Re: [sqlite] Query Planning Knowledge

2019-01-22 Thread David Raymond
Can't go into as much detail as you. But a couple comments. "primary key unique" is redundant, and will actually create a redundant unique index. "Do later version of SQLite manage to spot the constant propagation opportunity?" This requires way more algebra and proof than I think you realize.

Re: [sqlite] Query performance slower in 3.25

2018-09-30 Thread Stephen F. Booth
On Sat, Sep 29, 2018 at 5:33 PM Richard Hipp wrote: > On 9/29/18, Stephen F. Booth wrote: > > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: > > Thanks for the data sent off-list > > Your work-around is to add a plus sign "+" before the "a.id" in the > GROUP BY

Re: [sqlite] Query performance slower in 3.25

2018-09-29 Thread Richard Hipp
On 9/29/18, Stephen F. Booth wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: Thanks for the data sent off-list Your work-around is to add a plus sign "+" before the "a.id" in the GROUP BY clause. (And, BTW, shouldn't that really be an ORDER BY clause

Re: [sqlite] Query performance slower in 3.25

2018-09-29 Thread Richard Hipp
Could you please provide us with the database schema, or perhaps even a short script that demonstrates your problem, so that we can try to debug it? On 9/29/18, Stephen F. Booth wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: > > SQLite version 3.24.0 2018-06-04

Re: [sqlite] Query on TEMP view.

2018-08-28 Thread Keith Medcalf
There are a myriad of reasons for the behaviour you are seeing and they affect only performance and not correctness. In other words, you think that your UDF is more "expensive" to compute than the PPID == 2 test, and therefore the least expensive test should be performed first so that the

Re: [sqlite] Query optimisation

2018-08-24 Thread David Wellman
...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: 24 August 2018 15:43 To: SQLite mailing list Subject: Re: [sqlite] Query optimisation On 8/24/18, David Raymond wrote: > Running just "explain some query" will give you the virtual machine program > that it plans on using. You can then scan thr

Re: [sqlite] Query optimisation

2018-08-24 Thread Richard Hipp
On 8/24/18, David Raymond wrote: > Running just "explain some query" will give you the virtual machine program > that it plans on using. You can then scan through that to see what it's > doing. Note that the descriptions on the below page for those op codes are > sometimes really confusing and it

Re: [sqlite] Query optimisation

2018-08-24 Thread David Raymond
Running just "explain some query" will give you the virtual machine program that it plans on using. You can then scan through that to see what it's doing. Note that the descriptions on the below page for those op codes are sometimes really confusing and it can take a while to decypher what's

Re: [sqlite] Query on SQLite - VxWorks

2018-06-11 Thread Simon Slavin
On 9 Jun 2018, at 6:36pm, Guna Sekar wrote: > Is SQLite supports outfile query and dumps all data into specified file > format ? I don't understand your question, but you might want to use the command-line shell program to dump a database to a text file -- either as SQL commands or a CSV

Re: [sqlite] query planning - covering indices

2018-05-16 Thread Richard Hipp
On 5/15/18, Peter Johnson wrote: > > My understanding is that covering indices are more efficient, as the table > itself does not need to be scanned when all the required columns exist in > the covering index? That is often the case, but there are exceptions. > > Is it

Re: [sqlite] Query regarding CVE-2018-8740

2018-04-26 Thread Abroży Nieprzełoży
I think sqlite-autoconf-3230100 should be OK. http://www.sqlite.org/2018/sqlite-autoconf-3230100.tar.gz Always check download page for the newest version http://www.sqlite.org/download.html 2018-04-27 1:11 GMT+02:00, salil GK: > Hello > >We are using sqlite-autoconf-322 in our product.

Re: [sqlite] Query speed Regression: 3.15 much faster than 3.22 (Was: How to optimise a somewhat-recursive query? )

2018-03-18 Thread Richard Hipp
On 3/18/18, Jonathan Moules wrote: > > I can provide a small replication database if desired. Please do. Send it as an attachment directly to me. Also please send the exact text of the query that is running slowly. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread Cezary H. Noweta
Hello, On 2018-01-04 01:53, R Smith wrote: Not to mention that if you wait several years, depending on your processor/compiler, the integer 64 value might wrap around and x<=3 might become true once more, producing rows again  :) Unfortunately, it will be stuck when int becomes double (at

Re: [sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread R Smith
On 2018/01/04 12:36 AM, Richard Hipp wrote: On 1/3/18, Shane Dev wrote: sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) select * from cnt where x <= 3; [no sqlite> prompt, CPU utilization 25%] I assume sqlite is recursively adding rows to the

Re: [sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread Richard Hipp
On 1/3/18, Shane Dev wrote: > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x <= 3; > [no sqlite> prompt, CPU utilization 25%] > > I assume sqlite is recursively adding rows to the queue without considering > that the

Re: [sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread Shane Dev
I have just spotted a couple of typos in my email below. The first two common table expressions should have been as follows - with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt; with recursive cnt(x) as (select 1 union all select x+1 from cnt) select *

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-13 Thread mohan_gn
Hi Simon, Thank you for your suggestion. Will check it out. Regards, Mohan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-on-bug-fix-for-ticket-7ffd1ca1d2ad4ec-tp96175p96608.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-10 Thread Simon Slavin
On 10 Jul 2017, at 9:17am, mohan_gn wrote: > Since our product is already in field with older > version of SQLite, we want to know this bug's impact. We use simple queries > like 'select * from abc where col1=123 and col2=456'. We don't use any > JOINs, VIEWs, nested

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-10 Thread mohan_gn
Thank you for the reply. I know it has been fixed. Since our product is already in field with older version of SQLite, we want to know this bug's impact. We use simple queries like 'select * from abc where col1=123 and col2=456'. We don't use any JOINs, VIEWs, nested queries. The tables we use do

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-10 Thread Simon Slavin
On 10 Jul 2017, at 7:15am, mohan_gn wrote: > Could anyone please answer above query? Do you mean this bug ? If so, the bug was fixed in January. Please download the latest version of SQLite and see whether you can

Re: [sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

2017-07-10 Thread mohan_gn
Hi All, Could anyone please answer above query? Thank you very much, Mohan -- View this message in context: http://sqlite.1065341.n5.nabble.com/Query-on-bug-fix-for-ticket-7ffd1ca1d2ad4ec-tp96175p96575.html Sent from the SQLite mailing list archive at Nabble.com.

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Keith Medcalf
On Friday, 28 April, 2017 15:55, Simon Slavin wrote: > > The only difference is the explicit JOIN statement. I was under the > > impression that using this, vs. the way I wrote it, is a matter of taste > > that doesn’t affect the execution of the query. > SQLite computes

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Keith Medcalf
> -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Friday, 28 April, 2017 15:00 > To: SQLite mailing list > Subject: Re: [sqlite] Query plan gone haywire lays waste to my library's > performance >

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Igor Tandetnik
On 4/28/2017 4:37 PM, Jens Alfke wrote: CREATE TABLE docs (doc_id INTEGER PRIMARY KEY, docid TEXT UNIQUE NOT NULL, expiry_timestamp INTEGER); CREATE INDEX docs_docid ON docs(docid); For the record, this index is redundant. There's already an automatically created index on docs(docid), thanks

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Simon Slavin
On 28 Apr 2017, at 10:23pm, Jens Alfke wrote: > On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > >> What indexes do you have on these two tables ? I can’t recommend one >> without knowing which columns belong to which tables. > > I showed them at

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Jens Alfke
> On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > > What indexes do you have on these two tables ? I can’t recommend one without > knowing which columns belong to which tables. I showed them at the end of my first message. > First query rewritten for clarity: > […]

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Simon Slavin
On 28 Apr 2017, at 9:49pm, Jens Alfke wrote: > SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs > WHERE sequence>? AND current!=0 AND deleted=0 AND revs.doc_id = docs.doc_id > ORDER BY revs.doc_id, deleted, revid DESC > 0 0 0 SCAN TABLE revs USING

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

2017-04-28 Thread Jens Alfke
Sorry for the quick follow-up, but I’ve just run a quick test that does an EXPLAIN QUERY PLAN for every statement that gets compiled, in an empty database, and confirmed that the query plan comes out wrong right from the start: SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

2017-03-24 Thread Graham Holden
qlite-users@mailinglists.sqlite.org> Cc: "Subramaniyan, Ganesan (G.)" <ganesan.subramani...@visteon.com>, "Natanam, Karthigeyan (K.)" <karthigeyan.nata...@visteon.com> Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite Hi Dan, Custom memory management APIs

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

2017-03-24 Thread Hick Gunter
Anthrathodiyil, Sabeel (S.) Gesendet: Freitag, 24. März 2017 12:19 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: Subramaniyan, Ganesan (G.) <ganesan.subramani...@visteon.com>; Natanam, Karthigeyan (K.) <karthigeyan.nata...@visteon.com> Betreff: Re: [sqlite] QUER

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

2017-03-24 Thread Anthrathodiyil, Sabeel (S.)
@mailinglists.sqlite.org Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: > Hi, > We are facing dynamic memory pool corruption issue while using SQLite. Based > on our investigation we suspect SQLite freeing memory bypassing t

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

2017-03-23 Thread J Decker
On Thu, Mar 23, 2017 at 9:01 AM, Dan Kennedy wrote: > On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: > >> Hi, >> We are facing dynamic memory pool corruption issue while using SQLite. >> Based on our investigation we suspect SQLite freeing memory bypassing the

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

2017-03-23 Thread Dan Kennedy
On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: Hi, We are facing dynamic memory pool corruption issue while using SQLite. Based on our investigation we suspect SQLite freeing memory bypassing the memory management API'S registered as below. We have registered the memory allocation

Re: [sqlite] Query default lookaside pool size

2017-03-08 Thread Kim Gräsman
On Wed, Mar 8, 2017 at 2:41 PM, Simon Slavin wrote: > > On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > >> Is there a way to query SQLite build parameters at runtime, more >> specifically SQLITE_DEFAULT_LOOKASIDE? > >

Re: [sqlite] Query default lookaside pool size

2017-03-08 Thread Simon Slavin
On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > Is there a way to query SQLite build parameters at runtime, more > specifically SQLITE_DEFAULT_LOOKASIDE? Simon. ___

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul
> Why don't you just explicitly sort by bar.foo? > > > sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN > foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; > 0|0|0|SCAN TABLE bar > 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) > sqlite> I have

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Dimitris Bil
mitris From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Nico Williams <n...@cryptonector.com> Sent: Thursday, November 17, 2016 4:32 PM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy w

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Nico Williams
On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > Replacing JOIN does not help either: > > sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = > foo.id ORDER BY id DESC LIMIT 0, 40; > selectidorder fromdetail >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Nico Williams
On Thu, Nov 17, 2016 at 09:54:01AM -0500, Richard Hipp wrote: > Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. > But due to a coding error, early versions of SQLite did not enforce > that, and so we have taken care not to enforce it on all subsequent > versions of SQLite to

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul
> On 11/17/16, Paul wrote: > > > >> On 11/17/16, Richard Hipp wrote: > >> > On 11/17/16, Paul wrote: > >> >> That's why there was a LEFT JOIN in the first place, but as it seems, > >> >> it > >> >> wasn't that good idea. > >> > > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN.

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Marc L. Allen
ers@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I'm not sure that's a valid trick, as bar.foo can be NULL, in which > case the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp
On 11/17/16, Paul wrote: > >> On 11/17/16, Richard Hipp wrote: >> > On 11/17/16, Paul wrote: >> >> That's why there was a LEFT JOIN in the first place, but as it seems, >> >> it >> >> wasn't that good idea. >> > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul
> On 11/17/16, Richard Hipp wrote: > > On 11/17/16, Paul wrote: > >> That's why there was a LEFT JOIN in the first place, but as it seems, it > >> wasn't that good idea. > > > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > > planner in SQLite will not reorder a CROSS

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp
On 11/17/16, Richard Hipp wrote: > On 11/17/16, Paul wrote: >> That's why there was a LEFT JOIN in the first place, but as it seems, it >> wasn't that good idea. > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > planner in SQLite will not

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp
On 11/17/16, Paul wrote: > That's why there was a LEFT JOIN in the first place, but as it seems, it > wasn't that good idea. Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query planner in SQLite will not reorder a CROSS JOIN. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp
day, November 17, 2016 9:32 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy > when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote:

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul
> On 11/17/16, Marc L. Allen wrote: > > Maybe I'm missing something, but... > > > > ORDER BY id > > > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > > to find enough records where the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Marc L. Allen
Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.c

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Richard Hipp
On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > > ORDER BY id > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > to find enough records

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Marc L. Allen
. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, November 17, 2016 8:53 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient st

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Paul
It's not a solution, because in your example, foo will be scanned until limit is reached. This may take considerable amount of time, if results are parse. Also, this solution is totally useless. It's a partial index on 'foo', meaning that I can know true or false having only rows of 'foo'. In

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

2016-11-17 Thread Hick Gunter
Maybe you are looking for SELECT id,baz from foo where exists( select 1 from bar where foo = id) ... which has the effect of easily extending to an arbitrary number of bar tables via additional exists subqueries that may be connected by logical operators -Ursprüngliche Nachricht- Von:

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Igor Korot
Laura, On Thu, Sep 8, 2016 at 5:22 AM, Laura BERGOENS wrote: > Hi everyone, > > So I've put indexes myself on the most used tables in my program, then > ANALYZE the db, and now it flies. > The automatic creation of index probably took a lot of time, and it affects >

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Laura BERGOENS
Hi everyone, So I've put indexes myself on the most used tables in my program, then ANALYZE the db, and now it flies. The automatic creation of index probably took a lot of time, and it affects even more performance when it's always same queries on same tables that are repeated over and over

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Simon Slavin
On 8 Sep 2016, at 7:27am, Laura BERGOENS wrote: > I took notes of everything you guys said, and I'll spend my morning doing > this : > > Running ANALYZE once per DB I'mUsing, Create indexes myself and contact DB > Browser to share them the thing. Do those two steps

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Laura BERGOENS
sers [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > Behalf Of Chris Locke > Sent: Wednesday, September 7, 2016 12:12 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Query time execution difference between my > application

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-07 Thread Bob McFarlane
Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Wednesday, September 7, 2016 12:12 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-07 Thread Bob McFarlane
ct: Re: [sqlite] Query time execution difference between my application and SQLiteBrowser Ok I didn't know abount ANALYZE existence, sorry for that. I just run ANALYZE then the query again in sqlite3.exe and this time it took like half a second. Should I close and re open the DB, cause maybe this

  1   2   3   4   5   6   7   >