Re: [sqlite] The performance of indexed select

On 6 Jan 2018, at 6:41am, Nick wrote: > I find it is indeed faster than t2(c). If you want to know which is the best index, create all the indexes you think might be good, run ANALYZE, then use EXPLAIN QUERY PLAN SELECT (rest of SELECT statement here)

Re: [sqlite] The performance of indexed select

Some simple SQLs: SELECT count(*) FROM t2 WHERE c = xx; (or d = xx) -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] The performance of indexed select

Thank you Simon. But I am still uncertain if it is a good way to replace column 'c'. CREATE TABLE t2(a INTEGER, b INTEGER, d INTEGER); or: CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d INTEGER); and then CREATE INDEX t2d ON t2(d); SELECT count(*) FROM t2 WHERE d = xx; I

Re: [sqlite] The performance of indexed select

Nick wrote: >I am trying to analysis the performance of indexed select. > >CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); >CREATE INDEX t2c ON t2(c); Show the query that you are trying to analyze. Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] The performance of indexed select

On 6 Jan 2018, at 3:32am, Nick wrote: > I think there may be much more leaf index b-tree pages whose header is > '0x0A' if the length of the content of index key 'c' is always 20-25 bytes, > as I notice the format of index inside sqlite consist of the index key and >

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hello, On 2018-01-05 21:56, David Raymond wrote: sqlite> explain select id from tbl where id = null; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 8 000

[sqlite] The performance of indexed select

I am trying to analysis the performance of indexed select. CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT); CREATE INDEX t2c ON t2(c); I think there may be much more leaf index b-tree pages whose header is '0x0A' if the length of the content of index key 'c' is always 20-25 bytes, as I notice

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > The primary difference is likely the test on b.c. /There was some text here that seems to have been lost and my browser opened a "super resume templates" page... maybe I've been already spectre-hacked? Anyway, I repost:/ The primary difference from what I see is that it

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > The primary difference is likely the test on b.c. Keith Medcalf wrote > I think the query is ill-conceived when written as an outer join. If anything, to reflect intentionality the query should have been written as such: SELECT FROM ( SELECT FROM a LEFT JOIN b ) WHERE

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

>To reiterate, Keith: to get the query to execute properly, I didn't >change >the LEFT JOIN to an INNER JOIN! >Nope, >I rewrote > >SELECT >FROM > a > LEFT JOIN b ON >WHERE b.c=5 This means: Take all rows of table a match with rows of table b on the key and where there is no match "fill"

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

To reiterate, Keith: to get the query to execute properly, I didn't change the LEFT JOIN to an INNER JOIN! Nope, I rewrote SELECT FROM a LEFT JOIN b ON WHERE b.c=5 to SELECT FROM a LEFT JOIN b ON AND b.c=5 WHERE b.c IS NOT NULL So I just added a redundant predicate and it runs

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Keith Medcalf wrote > but cannot be re-ordered for OUTER JOINS. Actually, I think order requirements (or rather, row grouping requirements, as far as I can paint it the requirement is just that all outer joined rows come in a bunch for each main join tuple) would not be violated if the join is

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

On 5 Jan 2018, at 9:03pm, Doug Currie wrote: > NULL is not equal to NULL, though NULL is NULL. > > sqlite> select NULL IS NULL; > > 1 > > sqlite> select NULL = NULL; > > > sqlite> To expand on this, in SQL NULL has a special meaning. It means "value unknown or

Re: [sqlite] Efficient query to count number of leaves in a DAG.

Something is seriously funky here. I'm getting the opposite, where your query appears to be going faster than mine. I used your queries there to populate nodes and edges, based on 1,000,000 nodes. I even added in the extra index which turns out isn't used anyway. With it all in memory my

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

>Thanks for acknowledging this, you are absolutely right, that's why I >stated >that no DB does perfect optimisations and that computing the >alternative >-OR- based closures are probably much harder to tackle. Also E. Pasma >pointed out the -OR- queries as I wrote them are not really

Re: [sqlite] Preventing a slow, determinstic function from running twice

On Fri, 5 Jan 2018 14:42:00 -0600 Rob Hoelz wrote: > On Fri, 5 Jan 2018 15:04:16 -0500 > Richard Hipp wrote: > > > On 1/5/18, r...@hoelz.ro wrote: > > > Hi SQLite users and devs, > > > > > > I have an application using SQLite which stores

Re: [sqlite] Efficient query to count number of leaves in a DAG.

Hi David, According to https://sqlite.org/lang_with.html, "Optimization note: ...if the example had used UNION instead of UNION ALL, then SQLite would have had to keep around all previously generated content in order to check for duplicates. For this reason, programmers should strive to use UNION

Re: [sqlite] Preventing a slow, determinstic function from running twice

On Fri, 5 Jan 2018 12:02:21 -0800 Jens Alfke wrote: > > On Jan 5, 2018, at 11:25 AM, r...@hoelz.ro wrote: > > > > … My unxz function is a little slow, and I've noticed that if I > > specify the function several times in a query, it gets run multiple > > times, even though

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

At the risk of preserving this thread well past its end of life cycle... On 2018/01/05 6:58 PM, Dinu wrote: Ryan, You cannot ask SQL a Method query, that's where my whole RDBMS understanding takes me. Everything you ask SQL is underpinned by a specific Method. Perhaps I should have been

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Cezary is correct, NULL is not equal to NULL, though NULL is NULL. sqlite> select NULL IS NULL; 1 sqlite> select NULL = NULL; sqlite> e ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Hwllo, On 2018-01-05 21:19, x wrote: Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL. SELECT ID FROM Tbl where ID = NULL It has no sense as ``='' returns NULL when one of operands is NULL. NULL evaluates to FALSE. Above query can be described as SELECT

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

I it's treating "is" as an operator on the value. So with the = version, it knows it can use the index to zip to where the nulls would be and ends pretty quick when it doesn't find any. With the IS, it thinks it has to check every value because indexes are sorted on =, <, > etc, and not on

Re: [sqlite] Preventing a slow, determinstic function from running twice

On Fri, 5 Jan 2018 15:04:16 -0500 Richard Hipp wrote: > On 1/5/18, r...@hoelz.ro wrote: > > Hi SQLite users and devs, > > > > I have an application using SQLite which stores XZ-compressed blobs > > of JSON from the Twitter API to minimize disk usage. My unxz > >

[sqlite] difference between 'ID IS NULL' and 'ID = NULL'

Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL. SELECT ID FROM Tbl where ID = NULL takes only a few milliseconds to run and the explain query plan shows it uses the primary key. SELECT ID FROM Tbl WHERE ID IS NULL takes around 350 milliseconds to run and the

Re: [sqlite] Preventing a slow, determinstic function from running twice

On 1/5/18, r...@hoelz.ro wrote: > Hi SQLite users and devs, > > I have an application using SQLite which stores XZ-compressed blobs of JSON > from the Twitter API to minimize disk usage. My unxz function is a little > slow, and I've noticed that if I specify the function several

Re: [sqlite] Preventing a slow, determinstic function from running twice

> On Jan 5, 2018, at 11:25 AM, r...@hoelz.ro wrote: > > … My unxz function is a little slow, and I've noticed that if I specify the > function several times in a query, it gets run multiple times, even though > it's deterministic. … > Is there a way to tell SQLite to only run a function once

[sqlite] Preventing a slow, determinstic function from running twice

Hi SQLite users and devs, I have an application using SQLite which stores XZ-compressed blobs of JSON from the Twitter API to minimize disk usage. My unxz function is a little slow, and I've noticed that if I specify the function several times in a query, it gets run multiple times, even though

Re: [sqlite] new Error database disk image is malformed

I'm coding that now. I never thought about it. Thank you! -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Friday, January 5, 2018 1:34 PM To: SQLite mailing list Subject:

Re: [sqlite] new Error database disk image is malformed

> On Jan 4, 2018, at 6:29 PM, Peter Da Silva > wrote: > > Since you're I/O bound on socket connections, and not CPU or database bound, > you might want to just have one database thread that communicates using > native inter-thread messaging to pass out work

Re: [sqlite] Efficient query to count number of leaves in a DAG.

Hmm. Maybe try yours with union instead of union all? Though if there's only 1 path between any pair of nodes that shouldn't make too much difference. Otherwise I'm getting low on ideas. What're the record counts for nodes and edges? -Original Message- From: sqlite-users

[sqlite] Explain query plan output and recursive CTE's

Quick thing I noticed looking at some explain query plan outputs. When you get to the compound subqueries part in a recursive CTE it always seems to give the subquery numbers as 0 and 0. I’m not sure if that’s a bug, a limitation, or intended behavior. There're no issues with the queries

Re: [sqlite] Loadable extension with shared state

On 5 Jan 2018, at 12:12pm, Lifepillar wrote: > I can make application functions access the context only for reading, > if that matters. Nope. Reading vs. writing doesn’t matter. You treat them both the same. Nor does it matter whether you’re doing something trivial

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Short sum-up: - The -OR- alternative I provided is not semantically equivalent, I will work on one that is :) - The other one, without the -OR- (second post) still stands. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Richard, Thanks for acknowledging this, you are absolutely right, that's why I stated that no DB does perfect optimisations and that computing the alternative -OR- based closures are probably much harder to tackle. Also E. Pasma pointed out the -OR- queries as I wrote them are not really

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com: >> Anyway the two queries return the same set of rows. > >> This test also show a small semantic difference in the two queries. >> The set of rows is the same but the second query leaves certain >> details null if only one of

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

> Anyway the two queries return the same set of rows. > This test also show a small semantic difference in the two queries. > The set of rows is the same but the second query leaves certain > details null if only one of the OR conditions is true. That occurs in > row 1. You're

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

Dinu wrote: Hi all, I've ran into an optimisation problem with a double-left join that works as an "either" clause. The query is as follows: SELECT * FROM a LEFT JOIN b ON LEFT JOIN c ON WHERE b.someId IN (1,2,3) OR c.someId IN (4,5) This results in a bloated execution plan: SEARCH

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

On 1/4/18, Dinu wrote: > I think it's every DB's intention to optimize as best possible > a query into an execution plan. None does it perfectly, but all try to, very > hard. There are trade-offs here. How much slower are you willing for sqlite3_prepare() to run in order

Re: [sqlite] Loadable extension with shared state

On 03/01/2018 15:58, Lifepillar wrote: On 03/01/2018 15:48, Richard Hipp wrote: On 1/3/18, Lifepillar wrote: Consider an extension that has some shared state, say a global `context` struct, whose value is used by a few user-defined SQL functions. Besides, assume that

Re: [sqlite] Search % using sqlite

Hi, Thanks a lot for the support. We are actually using sqlite3_snprintf() with the proper buffer size to make sure that overflow will not happen. and also bind operations. INSTR() seems to be the best option. It seems to be fine. Thanks a lot. Thanks and Regards Deepak

Re: [sqlite] Search % using sqlite

On 2018/01/05 10:14 AM, Rowan Worth wrote: For this simple search it's easier to replace "NAME LIKE ?" with "instr(NAME, ?) > 0", unless you need case insensitive matching. And in case you do wish for Case-Insensitive matching while using Rowan's method... "instr(UPPER(NAME), UPPER(?)) >

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

On 2018/01/05 4:24 AM, Dinu wrote: Thank you for your answer, Keith. I had my problem "fixed" before I wrote the first mail. Also with every problem I also provided the fix that worked, for anyone that might run into the same problem. However, it's difficult to not get a little frustrated with

Re: [sqlite] Search % using sqlite

Firstly this code is extremely dangerous. What would happen if acInputString contained this string? ';DROP TABLE AUDIO; SELECT ' It's best practice to use bound parameters to prevent this kind of problem, ie. sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, , NULL);