I noticed this, IS is not treated like = for optimization:
SQLITE> CREATE TABLE t(f INTEGER);
SQLITE> CREATE INDEX t_f ON t(f) WHERE f IS NOT NULL;
SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f = 1;
QUERY PLAN
`--SEARCH TABLE t USING COVERING INDEX t_f (f=?)
SQLITE> EXPLAIN QUERY PLAN SELECT
On 5 Feb 2018, at 2:54pm, John Found wrote:
> It is clear now. But should I define an index that contains all fields used
> in the query?
>
> Something like:
>
>create index idxPostsComplex on posts(threadid, userid, Content, postTime,
> ReadCount);
>
> Actually I tried and the query use
On 2/5/18, John Found wrote:
>
> Actually I tried [adding a new index] and the query uses this index
> without problems (and the
> performance seems to be good).
>
> But what are the disadvantages of such approach? (except the bigger database
> size, of course)
(1) the database file is larger.
(
It is clear now. But should I define an index that contains all fields used in
the query?
Something like:
create index idxPostsComplex on posts(threadid, userid, Content, postTime,
ReadCount);
Actually I tried and the query uses this index without problems (and the
performance seems to be
list
Subject: [sqlite] Optimization - don't understand.
The following query:
explain query plan
select
U.nick,
U.id,
U.av_time,
T.Caption,
P.id,
-- P.ReadCount,
-- P.Content,
-- P.postTime,l
T.Caption
from Posts P
left join Th
On 2/5/18, John Found wrote:
> The following query:
>
> explain query plan
> select
> U.nick,
> U.id,
> U.av_time,
> T.Caption,
> P.id,
> -- P.ReadCount,
> -- P.Content,
> -- P.postTime,l
> T.Caption
> from Posts P
> left join Thre
The following query:
explain query plan
select
U.nick,
U.id,
U.av_time,
T.Caption,
P.id,
-- P.ReadCount,
-- P.Content,
-- P.postTime,l
T.Caption
from Posts P
left join Threads T on P.threadID = T.id
left join ThreadTags TT on
Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp:
> On 4/14/17, Wolfgang Enzinger wrote:
>>
>> Thank you Richard. I have to admit that it took me quite a while and also
>> reading the comment for check-in [1838a59c] several times to really
>> understand your explanation. Duh, that's tricky
Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp:
> But I've spent Good Friday working around it.
A thousand thanks! :-)
> Please try using the tip of the left-join-view branch
> (https://www.sqlite.org/src/timeline?c=left-join-view) and let me know
> if that version works better for you.
On 4/14/17, Wolfgang Enzinger wrote:
>
> Thank you Richard. I have to admit that it took me quite a while and also
> reading the comment for check-in [1838a59c] several times to really
> understand your explanation. Duh, that's tricky indeed!
>
But I've spent Good Friday working around it. Pleas
Am Fri, 14 Apr 2017 10:59:25 -0400 schrieb Richard Hipp:
> Performing this rewrite of a view into a simple LEFT JOIN is trickier
> than it seems at first glance. The rewrite works for the example you
> provide. But subtle changes to the view can make the rewrite invalid.
> For example:
>
> CREA
On 4/14/17, Wolfgang Enzinger wrote:
>
> CREATE VIEW z AS SELECT
> fk,
> (flags&1) AS odd,
> (flags&2)>>1 AS even,
> (flags&4)>>2 AS prime
> FROM y;
>
> Now using the VIEW z in a JOIN results in a full table scan on TABLE y
> despite a WHERE clause and an appropriate INDEX:
>
> EXPLAIN Q
Hello !
Maybe this problem would be the reason of getting bad query plans when
joining views too.
Cheers !
On 14/04/17 08:03, Wolfgang Enzinger wrote:
Hello,
given the following:
CREATE TABLE x(
pk INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE y(
Hello,
given the following:
CREATE TABLE x(
pk INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE y(
fk INTEGER REFERENCES x(pk),
flags INTEGER
);
CREATE INDEX yy ON y(fk);
CREATE VIEW z AS SELECT
fk,
(flags&1) AS odd,
(flags&2)>>1 AS even,
(flags&4
Dear SQLIte developers,
Here is a small patch for the linked-list merge sorter in SQLite
to sort N items,
It will save about 2*N CPU instructions by eliminate unnecessary null
pointer check,
Regards
make test passed.
fossil diff
Index: src/pcache.c
==
On 5/20/16, Quan Yong Zhai wrote:
> Dear SQLIte developers,
>
> Here is a small patch for the linked-list merge sorter in SQLite
> to sort N items,
> It will save about 2*N CPU instructions by eliminate unnecessary null
> pointer check,
Thank you for the optimization suggestion.
The merge-sor
Hello, I like to post this remark again as it seems closely related to
"Query flattening for left joins involving subqueries on the right-
hand side".
I have a complete different reason though. For playing with sudoku
solving, I have a table representing the digits 1..9:
CREATE TABLE digi
Op 13 mrt 2015, om 00:03 heeft Wolfgang Enzinger het volgende
geschreven:
> Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma:
>
>> Actually query one appears slightly faster,
>> Searching the PK index is faster as that is always a COVERING index.
>
> I was under the impression that the opposit
Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma:
> Actually query one appears slightly faster,
> Searching the PK index is faster as that is always a COVERING index.
I was under the impression that the opposite is true, but I wasn't sure
about that.
> From the secunsary indexes only a part o
>> Actually query one appears slightly faster,
>> Searching the PK index is faster as that is always a COVERING index.
>
>I was under the impression that the opposite is true, but I wasn't sure
>about that.
The primary key is only a covering index if you are only accessing fields
comprising the
I personally would use "... EXISTS ( SELECT 1 ...", which requires no extra
columns to be acessed at all.
-Urspr?ngliche Nachricht-
Von: Wolfgang Enzinger [mailto:sqlite at enzinger.net]
Gesendet: Samstag, 07. M?rz 2015 19:25
An: sqlite-users at mailinglists.sqlite.org
Betref
Op 7 mrt 2015, om 19:24 heeft Wolfgang Enzinger het volgende geschreven:
> Hi dev team,
>
> not sure if this is actually a useful hint, but ...
>
> CREATE TABLE a(a1 INTEGER PRIMARY KEY);
> INSERT INTO a VALUES (1),(2),(3);
> CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY);
> IN
Hi dev team,
not sure if this is actually a useful hint, but ...
CREATE TABLE a(a1 INTEGER PRIMARY KEY);
INSERT INTO a VALUES (1),(2),(3);
CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY);
INSERT INTO b VALUES (1,11),(2,22),(3,33);
CREATE UNIQUE INDEX b_ui ON b(a1,b1);
CREATE TA
Hello,
Suppose one has an expression on the columns of a single table, say x
+y, and that this expression occurs in multiple queries. Then it is
attractive to define it at a single place, using a view:
create view v as select *, x+y as a from t;
I had hoped that substituting such a view
Dear Richard,
First of all, thank you for your fast answer. I am Thiago's advisor and we
are facing some problems trying to optimizing SQLite, specially to
understand its code and data structure.
So, in order to help us, I would kindly ask you to provide us any
documentation about the code and / o
...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, March 14, 2011 10:50 AM
To: Thiago Gregolon; General Discussion of SQLite Database
Cc: Fabiano Baldo
Subject: EXT :Re: [sqlite] Optimization SQLite
Email forwarded to the sqlite-users mailing
Email forwarded to the sqlite-users mailing list.
I use gcc and gdb and plain old text editor. I am not able to recommend an
IDE since I don't use one.
On Mon, Mar 14, 2011 at 11:16 AM, Thiago Gregolon
wrote:
> Good morning,
>
> My name is Thiago from UDESC - Universidade do Estado de Santa Cat
On 3 Dec 2009, at 6:26pm, mr_orange wrote:
> I am trying to optimize the speed of my SQLite transactions. The goal is to
> beat query and insertion times that we have with MS SQL. I guess there are 2
> main issues:
>
> 1) The query times are faster when I do simple select statements on a large
>
lite/SQLite_optimization_FAQ.html#pragma-cache_size
SQLite Optimization
for advice on how to optimize SQLite. Does anyone know of any other
resources on the topic of optimizing SQLite?
Any advice welcome, thanks.
--
View this message in context:
http://old.nabble.com/SQLite-Optimization-tp266
Thank you, Igor. Processing time: 5 seconds. :-)
Igor Tandetnik wrote:
> Tim Romano wrote:
>
>> I've read http://www.sqlite.org/optoverview.html but don't find my
>> answer there.
>>
>> In the following query, WOIDS has 4 million rows and CORNFIX has
>> 25,000 rows.
>>
>> UPDATEWOIDS
>>
I solved my "inner/outer" problems by compiling Sqlite with
SQLITE_ENABLE_STAT2=1.
That flag makes it better at choosing the inner table!
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Wed, 11 Nov 2009 12:03:06 -0500
> Subject: Re: [sqlite] optimiza
Tim Romano wrote:
> I've read http://www.sqlite.org/optoverview.html but don't find my
> answer there.
>
> In the following query, WOIDS has 4 million rows and CORNFIX has
> 25,000 rows.
>
> UPDATEWOIDS
> SET corn = 1
> WHERE EXISTS
> (
> SELECT *
> FROM CORNFI
I've read http://www.sqlite.org/optoverview.html but don't find my
answer there.
In the following query, WOIDS has 4 million rows and CORNFIX has 25,000
rows.
UPDATEWOIDS
SET corn = 1
WHERE EXISTS
(
SELECT *
FROM CORNFIX
WHERE (cornfix.col_1 = woids.t
Mark Gilbert wrote:
>
> In fact we stumbled across the solution, and I am amazed we didnt
> think of it earlier, and no-one suggested it. Basically our LEAVES
> table doesn't have an Index !!
>
> As soon as we added an index, the process sped up by 17000% :-)
>
> However, I have some questi
shouldn't leafID be the primary key of your LEAVES table and thus already
indexed? What does your create table statement look like? I'd expect
CREATE TABLE Leaves (LeafID INTEGER PRIMARY KEY AUTOINCREMENT, ... other
columns ... )
As far as the create index failing, no idea there, sorry..
Sam
, 28 Feb 2008 21:25:35 -0500
>From: "Samuel Neff" <[EMAIL PROTECTED]>
>Subject: Re: [sqlite] Optimization Question for SQLite Experts
>To: "General Discussion of SQLite Database"
>
>Here's two suggestions. First the simple suggestion is instead of th
You obviously have a set of UID's at the time of the loop, how about
creating a huge select .. from where ...IN (list_of_uids_comma_separated)?
It'll be one single query (or you can break it down into blocks of 50,
or 100, etc).
Will save the overhead of generating the queries over and over again
Here's two suggestions. First the simple suggestion is instead of this..
for (z=0;z wrote:
> Folks.
>
> Looking for some advice from hardened SQliters...
>
> ...
For each twig we have to find all the leaves. The Leaves table has
> maybe 15000 records and we have a query where we search the Le
Folks.
Looking for some advice from hardened SQliters...
- Our application uses an SQLite 3.4.1 database with 8 tables. 1 of
the tables may contain tens or maybe hundreds of thousands of records
with about 30 fields.
- The tables form a linked tree type hierarchy where one table is the
trunk
Mark Gilbert wrote:
>> > - We don't currently prepare SQL statements in advance, would this
>>> technique benefit from somehow preparing statements *before* that
>>> thread gets the lock on the database ? Can we have multiple threads
>>> using the SAME database connection preparing SQL Queries
>
> > - We don't currently prepare SQL statements in advance, would this
>> technique benefit from somehow preparing statements *before* that
>> thread gets the lock on the database ? Can we have multiple threads
>> using the SAME database connection preparing SQL Queries at the same
>> time
Am 20.02.2008 um 14:03 schrieb Mark Gilbert:
> Folks.
>
> Our application uses SQlite on Mac OSX. It is a central data hub for
> a larger infrastructure and manages data coming in from some clients,
> and requests for data from other clients, using our own XML based
> protocols via TCPIP.
>
> It
Folks.
Our application uses SQlite on Mac OSX. It is a central data hub for
a larger infrastructure and manages data coming in from some clients,
and requests for data from other clients, using our own XML based
protocols via TCPIP.
Its somewhat like a web server with a backend system supplyi
--- Sean Cunningham <[EMAIL PROTECTED]> wrote:
> I have very large datasets and have found that the built in union,
> intersect, and except operations do not seem to use indices
> (would be happy to be proven wrong here). As such, they
> are not very speedy with large large data sets.
A patch
On 6/18/07, Sean Cunningham <[EMAIL PROTECTED]> wrote:
There was talk in the mailing list a while back about creating a new
operator that would act as a superset of '==' which would treat
NULL==NULL as True. I have seen this in some other database.
Anybody know if this is on the roadmap?
It wo
On Jun 15, 2007, at 5:27 PM, Scott Hess wrote:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value union
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS
NU
> select tableA.path, tableA.value from tableA,tableB where
> tableA.path=tableB.path and (tableA.value=tableB.value or
> (tableA.value IS NULL AND tableB.value IS NULL));
>
> It's possible that won't use an index, either, due to the OR, in which
> case you could try a union between a select with i
You can use something like:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and (tableA.value=tableB.value or
(tableA.value IS NULL AND tableB.value IS NULL));
It's possible that won't use an index, either, due to the OR, in which
case you could try a union betw
EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 15, 2007 1:26:49 PM
Subject: [sqlite] Optimization of equality comparison when NULL involved
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
c
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple insert
Hello to all!
How can I optimize this query
select * from table where col1 = 5 && col2 = 15;
If I know that col2 I have 15 rows and col1 I have just 100 rows, I
know that because I save that data in another table.
How can I do for search first where is minus number of result?
The two co
"Pam Greene" <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I'm working on a system to add full-text indexing on top of SQLite in a
> semi-automated way. The general idea is that users will call an API to
> "register" a document table for indexing, and the system will take care of
> everything from the
Hi all,
I'm working on a system to add full-text indexing on top of SQLite in a
semi-automated way. The general idea is that users will call an API to
"register" a document table for indexing, and the system will take care of
everything from there.
When a row is added to a registered document ta
Sqlite 3.2.7 does not seem to perform a fairly straightforward database
optimization for queries
involving an ORDER BY clause with a LIMIT clause.
Given a table or view with a large number of rows, such as View1 below:
CREATE TABLE t1(a,b,c);
INSERT INTO "t1" VALUES(4, 5, 6);
INSERT INTO "
54 matches
Mail list logo
