Re: [sqlite] Optimization - don't understand.

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

Re: [sqlite] fts5 giving results for substring searches for Hindi content.

On 02/04/2018 11:39 AM, raj Singla wrote: Hi, -- create fts4 and fts5 tables create virtual table idx4 using "fts4" (content); create virtual table idx5 using "fts5" (content); -- insert 1 sample rows into eachinsert into idx4 (content) values ('नीरजा भनोट के कातिल पाकिस्तान की जेल में थे, फिर

[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 Threads T on P.threadID = T.id left join ThreadTags TT on

Re: [sqlite] Optimization - don't understand.

Is P.id an integer primary key? If so then it can get it from any index. Since the only field it needs from P is id, it can use an index which isn't defined on id. This can make it quicker, especially if P has a lot of fields bloating its size. Once you include the other fields of P in the

Re: [sqlite] [EXTERNAL] Optimization - don't understand.

The additional fields are not contained in the index idxPostsThreadUser, so SQLite is forced to read the original row instead of just the index. Your query is searching the complete posts table, joining all the threads, tags and users together, and then discarding those without a matching tag.

Re: [sqlite] Optimization - don't understand.

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

Re: [sqlite] sqlite 3.22.0 walro2 test failures on ppc64

On 02/05/2018 04:22 PM, Petr Kubat wrote: On 02/05/2018 08:41 AM, Petr Kubat wrote: Hi all, hitting some failures when building 3.22.0 on ppc64 boxes (both big-endian and little-endian) running Fedora Rawhide (full logs in [1][2]): Time: walro.test 135 ms ! walro2-1.3.2.2 expected: [0

Re: [sqlite] Optimization - don't understand.

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

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

I think you are optimizing the performance of a conceptually inefficient query. If you are looking for a recipe that contains apples, do you read the entire cook book, checking each recipe for apples? Maybe it is much more efficient to look up apples in the index of ingredients and retrieve

Re: [sqlite] Optimization - don't understand.

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

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

On Mon, 5 Feb 2018 15:08:33 + Hick Gunter wrote: > I think you are optimizing the performance of a conceptually inefficient > query. > > If you are looking for a recipe that contains apples, do you read the entire > cook book, checking each recipe for apples? Maybe it is

[sqlite] Vetting SQLite

All, I've been using/loving SQLite for years, but the use of open source software is highly discouraged where I work, and now I have to prove to our IT dept. that SQLite is reliable and secure. The reliable part is easy because there is enough information on the SQLite website about testing,

Re: [sqlite] sqlite 3.22.0 walro2 test failures on ppc64

On 02/05/2018 08:41 AM, Petr Kubat wrote: Hi all, hitting some failures when building 3.22.0 on ppc64 boxes (both big-endian and little-endian) running Fedora Rawhide (full logs in [1][2]): Time: walro.test 135 ms ! walro2-1.3.2.2 expected: [0 32768] ! walro2-1.3.2.2 got:  [0 65536] !

Re: [sqlite] Vetting SQLite

On 5 Feb 2018, at 11:02pm, Richard Hipp wrote: > On 2/5/18, Drago, William @ CSG - NARDA-MITEQ wrote: >> >> Most of the software we use here, Microsoft and other well-known and >> paid-for products, > > You know that every copy of Windows comes with

Re: [sqlite] Vetting SQLite

I believe it's only since Windows 8. On February 5, 2018 7:51:39 PM EST, Stephen Chrzanowski wrote: >I was surprised to see that statement, so, checking my system, this >isn't >true. Win7Pro-x64. Not with that filename anyways. Searching my >system >with the

Re: [sqlite] Vetting SQLite

On 2/5/18, Stephen Chrzanowski wrote: > I was surprised to see that statement, so, checking my system, this isn't > true. Win7Pro-x64. It's on Windows10. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] Vetting SQLite

I was surprised to see that statement, so, checking my system, this isn't true. Win7Pro-x64. Not with that filename anyways. Searching my system with the "Everything" tool, [ *sqlite3.exe ] comes up with DLLs that I've touched only. The DLL's I've dumped into the Windows directories exist in

Re: [sqlite] Vetting SQLite

On 6 Feb 2018, at 12:56am, J. King wrote: > I believe it's only since Windows 8. Seems likely. SQLite has been part of the Windows SDK since Windows 10 Anniversary Update [1], some time around August 2016. I find it plausible that SQLite was in Windows 8 but not in

Re: [sqlite] Vetting SQLite

On Mon, Feb 5, 2018 at 5:04 PM, Richard Hipp wrote: > On 2/5/18, Stephen Chrzanowski wrote: > > I was surprised to see that statement, so, checking my system, this isn't > > true. Win7Pro-x64. > > It's on Windows10. > M:\>dir c:\windows\SysWOW64\*sqlite*

Re: [sqlite] Vetting SQLite

I'm not aware of a statement or guarantee, but the Web site provides lots of evidence here: In particular, SQLite being used as part of aircraft software by Airbus should tell you something. On February 5, 2018 12:21:53 PM EST, "Drago, William @ CSG -

Re: [sqlite] Vetting SQLite

On Mon, 5 Feb 2018 17:21:53 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > All, > > I've been using/loving SQLite for years, but the use of open source software > is highly discouraged where I work, and now I have to prove to our IT dept. > that SQLite is reliable

Re: [sqlite] Vetting SQLite

On 5 Feb 2018, at 5:21pm, Drago, William @ CSG - NARDA-MITEQ wrote: > I've been using/loving SQLite for years, but the use of open source software > is highly discouraged where I work, and now I have to prove to our IT dept. > that SQLite is reliable and secure. The

Re: [sqlite] Vetting SQLite

On Mon, 2018-02-05 at 09:39 -0800, Jens Alfke wrote: > > On Feb 5, 2018, at 9:21 AM, Drago, William @ CSG - NARDA-MITEQ > liam.dr...@l3t.com> wrote: > > > > The reliable part is easy because there is enough information on > > the SQLite website about testing, but what about security? > > Open

Re: [sqlite] Vetting SQLite

> On Feb 5, 2018, at 9:21 AM, Drago, William @ CSG - NARDA-MITEQ > wrote: > > The reliable part is easy because there is enough information on the SQLite > website about testing, but what about security? Open source software is more secure than closed source, since

Re: [sqlite] Vetting SQLite

Hi, On Mon, Feb 5, 2018 at 11:41 AM, Simon Slavin wrote: > On 5 Feb 2018, at 5:21pm, Drago, William @ CSG - NARDA-MITEQ > wrote: > >> I've been using/loving SQLite for years, but the use of open source software >> is highly discouraged where I

Re: [sqlite] Auto Index Warnings; key on deterministic functions

On Mon, Feb 5, 2018 at 6:28 PM, Keith Medcalf wrote: > > That is because you do not have an index on the tableB child key of the > relation (fk). This is required. see the lint command in a command line > shell near you. > > You do realize that a LEFT JOIN b is syntactic

[sqlite] Auto Index Warnings; key on deterministic functions

I have a couple tables like... create table tableA ( pk PRIMARY KEY, dataA ) create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES tableA(pk) ON DELETE CASCADE ) if the table was also ON UPDATE CASCADE could it slave to the same index as primary key? doing a LEFT JOIN on the tables I

Re: [sqlite] Auto Index Warnings; key on deterministic functions

That is because you do not have an index on the tableB child key of the relation (fk). This is required. see the lint command in a command line shell near you. You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN b which means, in English, include all the rows of a

[sqlite] 1st Call For Papers - 25th Annual Tcl/Tk Conference (Tcl'2018)

Hello SQLite Users, fyi ... 25th Annual Tcl/Tk Conference (Tcl'2018) http://www.tcl.tk/community/tcl2018/ October 15 - 19, 2018 Crowne Plaza Houston River Oaks 2712 Southwest Freeway, 77098 Houston, Texas, USA Important Dates: Abstracts and proposals due August 20, 2018 Notification to

Re: [sqlite] Vetting SQLite

To all that replied, thank you. Open source, not open contribution is a plus, so is the wide deployment and well known users (Airbus). There were many other good ideas mentioned like examining the source for network calls, etc. All of this will help me build a case in favor of SQLite. No one

Re: [sqlite] Vetting SQLite

On 2/5/18, Drago, William @ CSG - NARDA-MITEQ wrote: > > Most of the software we use here, Microsoft and other well-known and > paid-for products, You know that every copy of Windows comes with SQLite preinstalled, right? C:\Windows\System32\winsqlite3.dll -- D. Richard

Re: [sqlite] Vetting SQLite

On 2/5/18, Drago, William @ CSG - NARDA-MITEQ wrote: > All, > > I've been using/loving SQLite for years, but the use of open source software > is highly discouraged where I work, and now I have to prove to our IT dept. > that SQLite is reliable and secure. The reliable part

Re: [sqlite] Vetting SQLite

On Mon, 5 Feb 2018, Jens Alfke wrote: You can very easily prove that SQLite contains no networking code, so it’s incapable of accessing any network. Just search through sqlite3.c looking for the names of the system calls needed to open a socket; they don’t appear. Or more rigorously, use a