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

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 http://mailinglists.sqlite.org/cgi-bin/ma

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

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 ?

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 optimizer

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

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 ?

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 proces

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

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 you

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

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 ?

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 the

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

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 pl

Re: [sqlite] Query regarding Sqlite 3.30.1

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 d...@

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

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 Behal

Re: [sqlite] Query for Many to Many

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 inte

Re: [sqlite] Query for Many to Many

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, table2

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

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 the

Re: [sqlite] Query for Many to Many

" 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

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

; > > 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] Query planner regression with FTS4: slower path is chosen

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 search

Re: [sqlite] Query for Many to Many

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

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 aut

Re: [sqlite] Query for Many to Many

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

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

Re: [sqlite] Query Builder Access

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 Qu

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

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

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

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 lis

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

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 request

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

prüngliche 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 Gun

Re: [sqlite] Query Regression IN and Virtual Tables

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 VIR

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

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 dyn

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

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 colu

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

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

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 n

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

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

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

Original 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

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

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

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 > integrity?

Re: [sqlite] Query Planning Knowledge

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

Re: [sqlite] Query Planning Knowledge

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 b

Re: [sqlite] Query Planning Knowledge

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 to

Re: [sqlite] Query Planning Knowledge

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

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

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 clau

Re: [sqlite] Query performance slower in 3.25

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 instea

Re: [sqlite] Query performance slower in 3.25

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.

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 more

Re: [sqlite] Query optimisation

-boun...@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 sca

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

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 goi

Re: [sqlite] Query on SQLite - VxWorks

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 fil

Re: [sqlite] query planning - covering indices

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 correct to say that exampl

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

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. R

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

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

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

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 queue without considering

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

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 subsequent SELECT only n

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

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 * from

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

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

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 queries. The tables we use

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

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

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 reproduce the bug. Simon. ___

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

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

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 two-table searches usin

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

oʎ ɟı > -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 > perfor

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

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

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 the end of my first message. Sorry, I mis

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

> 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: > […] Should be more like >

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

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 INDEX revs_by_docid_re

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

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 revs

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

G.)" , "Natanam, Karthigeyan (K.)" Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite Hi Dan, Custom memory management APIs registered are being used by SQLite. We suspect SQLite, in certain cases is freeing allocated memory, not using the registered API to free. A

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

Anthrathodiyil, Sabeel (S.) Gesendet: Freitag, 24. März 2017 12:19 An: SQLite mailing list Cc: Subramaniyan, Ganesan (G.) ; Natanam, Karthigeyan (K.) Betreff: Re: [sqlite] QUERY: Usage of malloc and free in SQLite Hi Dan, Custom memory management APIs registered are being used by SQLite. We suspect

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

@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

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 >> memory management API'

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 the memory management API'S registered as below. We have registered the memory allocation r

Re: [sqlite] Query default lookaside pool size

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? > > Ah. Love it, thank

Re: [sqlite] Query default lookaside pool size

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. ___ sqlite-users mailing list

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

> 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 expla

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

Dimitris From: sqlite-users on behalf of Nico Williams Sent: Thursday, November 17, 2016 4:32 PM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On Thu, Nov 17, 2016 at 04:48:2

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

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

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 p

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

> 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. Th

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

Yeah.. I know that. Missed the PRIMARY KEY. :( Back to lurking. ;) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:54 AM To: SQLite mailing list Subject: Re: [sqlite] Query

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

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 query >> > planner

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

> 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 JOI

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

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 JOIN. But, as it t

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

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

t; To: SQLite mailing list > Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy > when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen wrote: >> Maybe I'm missing something, but... >> >> ORDER BY id >> >

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

> 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

Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > &

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

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 join fails. > > Ye

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

something. -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' Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '='

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

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 t

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

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

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

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 > even more performance when it'

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

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 again

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

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 the other way around. ANALY

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

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 > Subject: Re: [sqlite] Query time execution difference between my > application > and SQLiteBrowser > > &

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

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 Subject: Re: [sqlite] Query time execution difference

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

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 7, 2016 11:15 AM To: SQLite mailing list Subject: Re: [sqlite] Query time execution difference

  1   2   3   4   5   6   7   >