[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

Actually, doubt it's possible to use `content1_index` for ordering in your case once `content` rows are filtered by the IN operator. Most probably the index is just ignored here. Do you get any measurable performance improvement with vs w/o it? cheers, -Original Message- From:

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

Scott you can still set it up using setenv(Posix) or _putenv_s_(Windows). Can't you? On Wed, Aug 19, 2015 at 10:23 AM, Scott Doctor wrote: > > Another issue to consider is security. Some programs, such as mine, needs to > carefully control temporary files. Unless the user selects a specific >

[sqlite] Getting row number in a sorted list.

On 2015-08-20 07:44 AM, Anthrathodiyil, Sabeel (S.) wrote: > Hi, > I think there is ambiguity in the example I provided, I wouldn't need a > ranking in my case as I need the offset (think row number is misleading) of > the first record in the sorted list. > > Here is my use case. > Find the

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

Try to rewrite the query like this SELECT * FROM main WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') ORDER BY udate cheers, -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of

[sqlite] Compile warnings

Hi, I fully understand that removing warnings and still staying portable is hard. I recently had my problems with removing warnings for C code from the Visual Studio which is a nightmare since the C support of the Visual Studio is really bad (mixed declarations only with MSVC >= 2013). However in

[sqlite] Compile warnings

On 20 Aug 2015 at 17:46, Scott Doctor wrote: > My opinion is to keep it portable. The warning is there for a reason. > you are worried about performance yet are calling a function that does > nothing, which will take more memory cycles than a simple check for zero. > > Trying to memset a zero

[sqlite] Compile warnings

It's not a bug. It actually comes from the warning -Wmemset-transposed-args which is active with -Wall and I had a short look that it seems to be that there were several bugs with false positives for this warning and as far as I can understand the gcc developers did not rule out to issue the

[sqlite] Open DB from stream to use System.IO.Packaging.Package

On Aug 20, 2015, at 5:27 AM, Steffen Mangold wrote: > > What I want is to use the System.IO.Packaging.Package class to build a custom > file format. > Inside this a SQLite DB should be one System.IO.Packaging.PackagePart. It seems to me that SQLite and this .NET hierarchy are both trying to

[sqlite] Compile warnings

Hi, the problem is that this is a warning generated by the linker to warn the user that he potential swapped the arguments which it does since 0 occurs more often on the value side and memset(*,0,0) actually does nothing. Cheers, -Bernhard 2015-08-20 16:17 GMT+02:00 Scott Robison : > I suspect

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

On 08/20/2015 12:38 PM, David Waters wrote: > I have a large FTS4 table (around 200 million rows and growing). A simple > query (SELECT * FROM main WHERE main MATCH 'data') returns in less than a > second. However, if an ORDER BY is added (SELECT * FROM main WHERE main > MATCH 'data' ORDER BY

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

I did. My last post was based off testing it live - it works beautifully. Lighting quick and sorted without a temp B-TREE: EXPLAIN QUERY PLAN is now: SCAN TABLE content1 USING INDEX content1_index (~10 rows) EXECUTE LIST SUBQUERY 1 SCAN TABLE main VIRTUAL TABLE INDEX 6: (~0 rows) Dave On

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

Thanks Dan and Vladimir. A combination of the two approaches is required. I'll detail it here for future reference: Using the sub query against the FTS table as suggested: SELECT * FROM main WHERE rowid IN (SELECT rowid FROM main WHERE main MATCH 'data') ORDER BY udate Still causes the USE

[sqlite] Compile warnings

Hi, the warning which is mentioned in the ticket f51d9501800de5a0fb69d5048ce6662981b461ec still occurs also with newer gcc versions. The ticket was closed after a gcc bug report was opened. The gcc bug was closed due to a missing testcase. I had a quick look at the problem and it seems that the

[sqlite] Open DB from stream to use System.IO.Packaging.Package

Steffen Mangold wrote: > > is there a proper way to open a SQLiteConnection from a Stream object? > That is not directly supported. -- Joe Mistachkin

[sqlite] Open DB from stream to use System.IO.Packaging.Package

Hi, is there a proper way to open a SQLiteConnection from a Stream object? What I want is to use the System.IO.Packaging.Package class to build a custom file format. Inside this a SQLite DB should be one System.IO.Packaging.PackagePart. I don't want to copy the DB part to temp directory every

[sqlite] Compile warnings

On 8/20/15, Bernhard Schommer wrote: > Hi, > > the problem is that this is a warning generated by the linker to warn the > user that > he potential swapped the arguments which it does since 0 occurs more often > on > the value side and memset(*,0,0) actually does nothing. > My contention remains

[sqlite] Compile warnings

On Thu, Aug 20, 2015 at 10:46 AM, Scott Doctor wrote: > My opinion is to keep it portable. The warning is there for a reason. you > are worried about performance yet are calling a function that does nothing, > which will take more memory cycles than a simple check for zero. > I didn't say don't

[sqlite] Compile warnings

But does the simple solution of an if statement negatively impact performance? It may or may not, I really don't know. On the one hand, there is the overhead of the check for the condition. On the other, it avoids making a function call if the condition is false. On a third hand (I've run out,

[sqlite] Compile warnings

On 8/20/15, Bernhard Schommer wrote: > Hi, > > the warning which is mentioned in the ticket > f51d9501800de5a0fb69d5048ce6662981b461ec still occurs also with newer gcc > versions. The ticket was closed after a gcc bug report was opened. The gcc > bug was closed due to a missing testcase. > I had

[sqlite] ATTACH DATABASE statement speed

On Wed, Aug 19, 2015 at 6:53 PM, Simon Slavin wrote: > On 19 Aug 2015, at 10:46am, Paolo Bolzoni > wrote: > Just by itself, the above information is significant to the SQLite team. > Perhaps when you have had a chance to confirm it a new thread can be started > called 'SQLite database on

[sqlite] Compile warnings

On Thu, Aug 20, 2015 at 9:35 AM, Bernhard Schommer < bernhardschommer at gmail.com> wrote: > It's not a bug. It actually comes from the warning -Wmemset-transposed-args > which is active with -Wall and I had a short look that it seems to be that > there were several bugs with false positives for

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

Sorry. I should have specified that that 'udate' is one of the indexed columns of the FTS4 table main. The goal is to do a FTS query and be able to ensure the results are ordered the same each time (via the ORDER BY). It seemed at first to me that the FTS index contains what is needed for the

[sqlite] Compile warnings

My opinion is to keep it portable. The warning is there for a reason. you are worried about performance yet are calling a function that does nothing, which will take more memory cycles than a simple check for zero. Trying to memset a zero length is a bug, not the warning. Add an if statement

[sqlite] Compile warnings

Yeah, we saw this with Chromium, too. The patch we use is below. I'm with Dr Hipp that this is really more of a GCC issue. If it was literally a 0 constant, it would make sense to warn so that the code can be removed. But it's only a 0 if you optimize a certain way. -scott diff --git

[sqlite] Compile warnings

I suspect this is similar to another "recent" discussion about passing null pointers to mem* library functions. C89 allowed zero or null. C99 seems to forbid it. I'm going from memory here, can look at standards later if desired On Aug 20, 2015 8:08 AM, "Richard Hipp" wrote: > On 8/20/15,

[sqlite] SQLite database becomes corrupt on iOS

On 20 Aug 2015, at 6:22am, Scott Perry wrote: > That said, it's possible to corrupt a database by forcing the device to power > off (by holding the power and home buttons) while SQLite's writes are in > flight. This is equivalent to the conditions of a kernel panic. This is true only if your

[sqlite] Getting row number in a sorted list.

Hi, I think there is ambiguity in the example I provided, I wouldn't need a ranking in my case as I need the offset (think row number is misleading) of the first record in the sorted list. Here is my use case. Find the rownumber 'y' (offset) for a name in the name table which starts with

[sqlite] Is it possible to have query planner use FTS Index for ORDER BY

I have a large FTS4 table (around 200 million rows and growing). A simple query (SELECT * FROM main WHERE main MATCH 'data') returns in less than a second. However, if an ORDER BY is added (SELECT * FROM main WHERE main MATCH 'data' ORDER BY udate) it will never return (after 20 mins, I canceled

[sqlite] SQLite database becomes corrupt on iOS

On Aug 19, 2015, at 11:34 PM, Simon Slavin wrote: > > > On 20 Aug 2015, at 6:22am, Scott Perry wrote: > >> That said, it's possible to corrupt a database by forcing the device to >> power off (by holding the power and home buttons) while SQLite's writes are >> in flight. This is equivalent