> On 27 Jul 2015, at 10:18pm, Zsb?n Ambrus wrote:
>
> On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin wrote:
>> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus wrote:
>> I tried this once a couple of years ago, and both platforms use whatever the
>> expected variable name was for that OS. In other
On Mon, Jul 27, 2015 at 8:27 PM, R.Smith wrote:
>
>
> On 2015-07-27 08:09 PM, Simon Slavin wrote:
>
>> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau
>> wrote:
>>
>> create table TEST (
>>> a TEXT NOT NULL,
>>> a2 TEXT NOT NULL,
>>> b TEXT NOT NULL,
>>> c TEXT NOT NULL
>>> );
>>>
>>> create index
On Mon, Jul 27, 2015 at 12:28 PM, Richard Hipp wrote:
> On 7/27/15, Paolo Bolzoni wrote:
>> I found the temp_store_directory, but it is deprecated. So I was wondering,
>> what is the suggested alternative?
>
> Set the TEMP environment variable to the location of your temporary
> storage area.
On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus wrote:
> Does this work with the same environment variable name on both unix
> and windows? I'm asking because unix and windows programs each use
> different conventions for what environment variable to care about when
> determining the temporary
On 2015-07-27 08:09 PM, Simon Slavin wrote:
> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau
> wrote:
>
>> create table TEST (
>> a TEXT NOT NULL,
>> a2 TEXT NOT NULL,
>> b TEXT NOT NULL,
>> c TEXT NOT NULL
>> );
>>
>> create index IDX_TEST_1 on TEST(a,a2,b,c);
>>
>> insert into TEST(a,a2,b,c)
Yes, having:
create table TEST (
a TEXT NOT NULL,
a2 TEXT NULL,
b TEXT NOT NULL,
c TEXT NOT NULL
);
create index IDX_TEST_1 on TEST(c,b,a,a2);
create index IDX_TEST_2 on TEST(c,b,a);
create index IDX_TEST_3 on TEST(c,b,a2);
sqlite> explain query plan select * from TEST where (a = '123' or
ha yes thank you
create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);
sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';
0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?)
0|0|0|SEARCH TABLE TEST
On 27 Jul 2015, at 7:32pm, rotaiv wrote:
> Originally, I copied the database file from my PC to my Linux server. I
> thought it might be a carriage return or line feed issue so I recreated the
> database from scratch on my Linux server. Didn't change anything.
SQLite is completely platform
Hello,
I would like to know if this is possible to use an index in the following
case:
select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';
given the table:
create table TEST (
a TEXT NOT NULL,
a2 TEXT NOT NULL,
b TEXT NOT NULL,
c TEXT NOT NULL
);
create index
On 27 Jul 2015, at 7:34pm, Drago, William @ CSG - NARDA-MITEQ wrote:
> If case is not important would adding COLLATE NOCASE to column c improve
> performance?
Depends whether it would reduce the number of different 'chunks'. In other
words whether there really were any examples of the
On 2015-07-27 05:48 PM, Marc L. Allen wrote:
> When would that specific LEFT JOIN ever do anything except return NULLs for
> the right table? It only accepts rows from work where fpath is null, and
> only joins those rows where fpath = home.fpath. Since fpath must be null,
> home.fpath
On 27 Jul 2015, at 6:58pm, Sylvain Pointeau
wrote:
> create table TEST (
> a TEXT NOT NULL,
> a2 TEXT NOT NULL,
> b TEXT NOT NULL,
> c TEXT NOT NULL
> );
>
> create index IDX_TEST_1 on TEST(a,a2,b,c);
>
> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
> insert into
If case is not important would adding COLLATE NOCASE to column c improve
performance?
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org
On 2015-07-27 04:29 PM, rotaiv wrote:
>> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
>> staying relatively current. And please ignore my previous post. I somehow
>> managed to not register that you would doing a LEFT join, an equi-join.?
>>
> I am also OCD about
On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote:
> When would that specific LEFT JOIN ever do anything except return
> NULLs for the right table? It only accepts rows from work where
> fpath is null, and only joins those rows where fpath = home.fpath.
> Since fpath must be null, home.fpath
On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote:
> Sorry.. confused does sqlite allow comparison between NULLs?
No.
> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
>
> Should never match anything... it checks to see if work.fpath IS
> NULL and if the same
Or even better:
select fpath
from home
where not exists (select 1 from work where work.fpath=home.fpath and
work.ftype=home.ftype)
and ftype = 'f'
with a unique index on home (ftype, fpath) and a unique index on work (ftype,
fpath) of course.
> -Original Message-
> From:
On Mon, 27 Jul 2015 at 16:24, rotaiv wrote:
> I am more than open to a more logical and/or more efficient query if
> anyone has suggestions.
One alternative was posted by Igor, but it might end up as the same
query plan after the optimizer has done its thing (EXPLAIN will tell
you).
Adding
This was the error I got:
fts5_main.c:30: error: redefinition of typedef 'Fts5Global'
On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy wrote:
> On 07/28/2015 02:55 AM, Hinrichsen, John wrote:
>
>> Hi,
>>
>> I was not able to get the fts5 module to build from the versioned source
>> tarball for
Hi,
I was not able to get the fts5 module to build from the versioned source
tarball for this release (http://www.sqlite.org/2015/sqlite-src-3081100.zip
).
I was able to 'make fts5.c' following the instructions that reference the
"trunk" tarball.
Regards,
John Hinrichsen
--
This message
On 27 Jul 2015, at 3:04pm, Igor Tandetnik wrote:
> That's a common technique with LEFT JOIN
Thanks, Igor.
On 27 Jul 2015, at 1:44pm, rotaiv wrote:
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;
Can you verify
On 27 Jul 2015 at 13:44, rotaiv wrote:
> Where is the resource bottleneck that causes a simple query to never
> complete after 40 minutes
>
> I am using sqlite to synchronize files on my home PC and my work PC. I
> created a file listing of each computer and imported into separate tables.
And
> > That's a common technique with LEFT JOIN
>
> Thanks, Igor.
>
A bit MySQL specific, but shows some options
http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/
Regards
Andy Ling
On 27 Jul 2015, at 1:44pm, rotaiv wrote:
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;
Are you sure you got this right ? It uses work.fpath twice:
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath
> It's not the older SQLite taking so much longer, it's simply that the file
> created with a newer SQLite probably contains a SCHEMA or objects not
> readily understood or able to be optimized by the older engine. if the file
> was made with the old engine it would perform perfectly well in both
On 7/27/15, Sylvain Pointeau wrote:
> Hello,
>
> I would like to know if this is possible to use an index in the following
> case:
>
> select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';
>
>
> given the table:
>
> create table TEST (
> a TEXT NOT NULL,
> a2 TEXT NOT
On Mon, Jul 27, 2015 at 1:00 PM, Simon Slavin wrote:
>
> On 27 Jul 2015, at 7:32pm, rotaiv wrote:
>
> > Originally, I copied the database file from my PC to my Linux server. I
> > thought it might be a carriage return or line feed issue so I recreated
> the
> > database from scratch on my
Dear list,
I have a somewhat large sqlite3 db (about 120GB) and I need to create
some indexes on it, but I get "database or disk is full" while I have
still about 300GB of free disk space.
However the /tmp directory is only 5GB so I suspect that sqlite3 has
not enough space there. I found the
Oh, jeeze. Wow.
And I know all that... I cleverly managed to ignore the "WHERE" and think it
was an AND because it was one the same line.
Duh.
Thanks.
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Hi,
as the maintainer of the SQLite RPMs on SUSE, I am currently faced
with a bug report concerning CVE-2015-3659[0].
>From the CVE's description it looks to me like the bug was in Apple's
authorizer callback rather than SQLite's authorization mechanism, can
anyone confirm this?
Thanks,
When would that specific LEFT JOIN ever do anything except return NULLs for the
right table? It only accepts rows from work where fpath is null, and only
joins those rows where fpath = home.fpath. Since fpath must be null,
home.fpath should never be equal.
-Original Message-
From:
Sorry.. confused does sqlite allow comparison between NULLs?
I mean...
LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
Should never match anything... it checks to see if work.fpath IS NULL and if
the same work.fpath is equal to something.
I get the weird feeling that
> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
> staying relatively current. And please ignore my previous post. I somehow
> managed to not register that you would doing a LEFT join, an equi-join.?
>
I am also OCD about updates. I installed Sqlite using yum right before
> Also, I agree with Simon that the query itself, and it's use of NULL, looks
> weird. And I don't see where it could find any matches because the "ON
> work.fpath = home.fpath" should never match if either fpath is NULL.
>
>
I confess, I understand basic SQL but it is not one of my strengths at
Problem solved. I updated the version on the server and it completes in 16
seconds.
To answer your question, I am using the find command to created a delimited
text file. I copy that file to my home PC then importing that file into a
sqlite database table. I use the same process to create a
Thank for reminding me to check the obvious. The server was using version
3.6.20. I manually upgraded to version 3.8.10.2 and the query completed in
16 seconds. Still not as fast as my home PC but given the age of the
hardware, this is understandable. I will take 16 seconds as opposed to 40+
That is correct - I am looking for records that are empty. I tried this
version of the SQL query as well and during my testing, the first example
seemed to be more efficient.
Also, I have version 3.8.10.2 on my home PC and version 3.6.20 on my server
at work. I am going to update the server
On 7/27/2015 9:58 AM, Simon Slavin wrote:
> So you're checking both to see that [work.fpath = home.fpath ] and to see
> that [work.fpath IS NULL]. This looks weird to me.
That's a common technique with LEFT JOIN - it's selecting home records
that lack a corresponding work record. In other
On 7/27/15, rotaiv wrote:
>
> My question is ... What is about my machine at home
> that allows Sqlite to query 2 million files so quickly?
>
Probably a newer version of SQLite. You didn't tell us the version
numbers for SQLite on the two machines.
--
D. Richard Hipp
drh at sqlite.org
On Mon, Jul 27, 2015 at 9:21 AM, rotaiv wrote:
> Problem solved. I updated the version on the server and it completes in 16
> seconds.
>
?WOW! That is an amazing difference. Makes me glad that I'm OCD about
staying relatively current. And please ignore my previous post. I somehow
managed to
On Mon, Jul 27, 2015 at 8:42 AM, Richard Hipp wrote:
> On 7/27/15, rotaiv wrote:
> >
> > My question is ... What is about my machine at home
> > that allows Sqlite to query 2 million files so quickly?
> >
>
?I missed the initial post, somehow.?
?Could there be an index on something on the
Where is the resource bottleneck that causes a simple query to never
complete after 40 minutes
I am using sqlite to synchronize files on my home PC and my work PC. I
created a file listing of each computer and imported into separate tables.
Then I used the following query to locate missing
On 7/27/15, Paolo Bolzoni wrote:
> Dear list,
>
> I have a somewhat large sqlite3 db (about 120GB) and I need to create
> some indexes on it, but I get "database or disk is full" while I have
> still about 300GB of free disk space.
>
> However the /tmp directory is only 5GB so I suspect that
On 7/27/15, Reinhard Max wrote:
> Hi,
>
> as the maintainer of the SQLite RPMs on SUSE, I am currently faced
> with a bug report concerning CVE-2015-3659[0].
>
> From the CVE's description it looks to me like the bug was in Apple's
> authorizer callback rather than SQLite's authorization
I am out of the office until 8/3/2015
If you have an urgent issue, please contact cabatecola at seacorp.com
Thanks,
Brian Willner
45 matches
Mail list logo
