Re: [sqlite] inserts, performance, file lock...

all settings: NameValue Modified auto_vacuum noneFalse automatic_index on False cache_size 2000False case_sensitive_like off False collation_list [NOCASE], [RTRIM], [BINARY] False count_changes off False default_cache_size 2000False

[sqlite] GROUP BY With ASC/DESC

Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing

Re: [sqlite] GROUP BY With ASC/DESC

On 11 Nov 2011, at 2:23am, dmp wrote: > I'm getting the following exception: > > SQLException: [SQLITE_ERROR] SQL error or missing database (near "ASC": > syntax error) > > with: > > SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price > '2' > GROUP BY color ASC ORDER BY name

Re: [sqlite] GROUP BY With ASC/DESC

dmp wrote: > I'm getting the following exception: > > SQLException: [SQLITE_ERROR] SQL error or missing database (near "ASC": syntax > error) > > with: > > SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price > '2' > GROUP > BY color ASC ORDER BY name ASC

Re: [sqlite] GROUP BY With ASC/DESC

On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote: > ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed to > achieve, anyway? Your query makes no sense to me. Igor, I started out to write exactly the same thing, but then I saw the diagram on

Re: [sqlite] GROUP BY With ASC/DESC

SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price > '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? Looks like a documentation bug. Indeed the

Re: [sqlite] GROUP BY With ASC/DESC

On Fri, Nov 11, 2011 at 02:24:54PM +0100, Jean-Christophe Deschamps scratched on the wall: > >Is this a bug or a query statement inaccuracy on my part? > > Looks like a documentation bug. Indeed the select-core diagram > incorrectly shows an ordering-term in the GROUP BY clause. It > should

Re: [sqlite] GROUP BY With ASC/DESC

On Fri, Nov 11, 2011 at 8:20 AM, Simon Slavin wrote: > > On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote: > > > ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed > to achieve, anyway? Your query makes no sense to me. > > Igor, I started out to write

[sqlite] JOIN vs IN

Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always joins a bit slower

[sqlite] GROUP BY With ASC/DESC

Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing

Re: [sqlite] GROUP BY With ASC/DESC

I think you want ORDER BY COLOR,NAME Since color is a primary key you can't have dups so what good is the group by? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

Simon Slavin wrote: > On 9 Nov 2011, at 8:03pm, Yuriy Kaminskiy wrote: > >> Look at: SELECT hex(X'1245005679'),hex(X'1245001234'); >> >> And compare: SELECT X'1245005679' LIKE X'1245001234'; 1 -- incorrect SELECT >> X'1245005679' = X'1245001234'; 0 -- correct SELECT X'1245005679' > >>

[sqlite] Segment merging in FTS and updates-deletes

Hi, I'm studying the indexing mechanism of FTS3/4, I can pretty much understand how doclists, terms, segments are created and stored, but one thing I can't grasp is about updating and deleting docs and keeping up the index up to date. From the source comments: [quote] ** Since we're using a

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 11:55 AM, Yuriy Kaminskiy wrote: > One way or other, "=", "LIKE" and "GLOB" results should be consistent. > If string is NUL-terminated, "=" should ignore everything after NUL. > If string is length-terminated, "LIKE" should not ignore bytes after NUL.

Re: [sqlite] inserts, performance, file lock...

On Thu, Nov 10, 2011 at 3:19 AM, yqpl wrote: > i did some test do check if indexes make it slow. instead of inserting to > disk database i use ":memory:" database - i have copied tables only - i > assume without indexes and then do inserts - and it works the same. UNIQUE

Re: [sqlite] inserts, performance, file lock...

On Fri, Nov 11, 2011 at 1:39 AM, yqpl wrote: > Nico Williams wrote: >> What's your page size? > > i have no access now to those files. but i didnt change any thing - so > default. You really want to set the page size to something decent -- at least the filesystem's preferred

Re: [sqlite] GROUP BY With ASC/DESC

> Message: 23 > Date: Fri, 11 Nov 2011 10:57:22 -0500 > From: da...@dandymadeproductions.com > To: sqlite-users@sqlite.org > Subject: [sqlite] GROUP BY With ASC/DESC > Message-ID: > <5ed601b698a020a8d790240cc05c8714.squir...@dandymadeproductions.com> > Content-Type:

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On 11 Nov 2011, at 6:09pm, Nico Williams wrote: > blob = blob should be a binary comparison > > blob = string should be a string comparison > > blob LIKE pattern should either treat the blob as a string or not, but > I don't see why either behavior should imply that blob = blob should > be

[sqlite] Quickest way to get an answer

My db has a column called "status". This can take one of 7 or so integer values from 0 to 7 or so. There are times when I need a quick answer to this question: are there any rows in the db for which status has value 0. I don't need to know how many, just whether there are any or not. What's the

Re: [sqlite] Quickest way to get an answer

On 11 Nov 2011, at 7:27pm, Tim Streater wrote: > select count(status) from mytable where status=0; > select count(status) from mytable where status=0 limit 1; > select status from mytable where status=0 limit 1; > > When doing this a number of times I see some seconds of CPU being taken; I

Re: [sqlite] [bug] LIKE operator ignores rest of string after NUL character

On Fri, Nov 11, 2011 at 1:16 PM, Simon Slavin wrote: > On 11 Nov 2011, at 6:09pm, Nico Williams wrote: >> blob = blob should be a binary comparison >> >> blob = string should be a string comparison >> >> blob LIKE pattern should either treat the blob as a string or not, but

Re: [sqlite] inserts, performance, file lock...

yes still slows down. Nico Williams wrote: > > On Thu, Nov 10, 2011 at 3:19 AM, yqpl wrote: >> i did some test do check if indexes make it slow. instead of inserting to >> disk database i use ":memory:" database - i have copied tables only - i >> assume without indexes

Re: [sqlite] inserts, performance, file lock...

On Fri, Nov 11, 2011 at 2:38 PM, yqpl wrote: > yes still slows down. Can you characterize it? All index inserts should slow down somewhat as the index grows since lookup and insertion will be O(logN) operations for b-trees, but also as your indexes grow larger than

Re: [sqlite] VACUUM

On Fri, Nov 11, 2011 at 00:52:23 +0100, Fabian wrote: > 2011/11/10 Richard Hipp > > Because when you are inserting the Nth row, SQLite has no idea of how many > > more rows will follow or how big the subsequent rows will be, so it has no > > way to reserve contiguous space

Re: [sqlite] Segment merging in FTS and updates-deletes

On Fri, Nov 11, 2011 at 9:58 AM, nobre wrote: > Hi, I'm studying the indexing mechanism of FTS3/4, I can pretty much > understand how doclists, terms, segments are created and stored, but one > thing I can't grasp is about updating and deleting docs and keeping up

[sqlite] HAVING and aggregate functions

Hi, I have a strange problem with an SQL query. I want to filter records by the result of a grouped column. SQLite doesn't complain but gives no result records in PHP PDO but the very same SQL does work fine in the SQLite shell on Windows. I have version 3.6.22 and an older 3.6 on the PHP side

[sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements

Embedded Sqlite3 questions: I want to load and prepare multiple statements, keep them prepared and when I want to use one of them, I will reset, bind and step. Can pre-prepare multiple independent statements, then run them one at a time at random? Thanks, this may be a newbie question for

Re: [sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements

Yes, works great! On 11/11/2011 4:24 PM, Matt Young wrote: Embedded Sqlite3 questions: I want to load and prepare multiple statements, keep them prepared and when I want to use one of them, I will reset, bind and step. Can pre-prepare multiple independent statements, then run them one at a

Re: [sqlite] General question on sqlite3_prepare, the bind and resets of prepared statements

On 11/11/2011 7:24 PM, Matt Young wrote: Embedded Sqlite3 questions: I want to load and prepare multiple statements, keep them prepared and when I want to use one of them, I will reset, bind and step. Make it bind, step and reset. Don't leave a statement active for a long time - it keeps the

Re: [sqlite] Quickest way to get an answer

>  select count(status) from mytable where status=0; >  select count(status) from mytable where status=0 limit 1; These two are identical because query always return one row thus making "limit 1" a no-op. >  select status from mytable where status=0 limit 1; This one will have the best

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 11:58 AM, Petite Abeille wrote: >> It returns the same results, but it doesn't seem much faster. Is there any >> performance difference to be expected from using IN instead of JOIN, or >> does SQLite internally rewrite JOIN queries to something

Re: [sqlite] JOIN vs IN

On Nov 12, 2011, at 1:58 AM, Pavel Ivanov wrote: > No, "exists" in this case will change query plan significantly and > performance can degrade drastically as a result. Why would that be? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] JOIN vs IN

Fabian wrote: Suppose I have two tables, and I want to have look for a value in the first table, but display the columns from the second table. The most obvious way would be joining them on rowid. But I don't need to SELECT any columns from the first table, and it's a FTS4 table (which always

[sqlite] Referencing subquery several times

Hi everybody, Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT * FROM a) OR c IN (SELECT *

Re: [sqlite] JOIN vs IN

On Fri, Nov 11, 2011 at 8:06 PM, Petite Abeille wrote: >> No, "exists" in this case will change query plan significantly and >> performance can degrade drastically as a result. > > Why would that be? How would you rewrite the query using exists? The only thing I have in

Re: [sqlite] Referencing subquery several times

> I know this could be done more easily, but I have to keep this > structure (i.e. cannot JOIN for instance). My question is, how could I > avoid repeating the subqueries after the AND NOT, since they will > return the same set as the previous ones? With your restriction on query structure you

Re: [sqlite] Referencing subquery several times

On 11/11/2011 9:24 PM, Alexandre Courbot wrote: Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT *

[sqlite] Any thoughts on optimizations for this query?

Hi all, We are struggling to find a way to rework this query in a way that performs efficiently for large data sets. The goal is to find the top x most active senders of e-mails within a date range. The killer of this query is the ORDER BY clause. Without it the results are quick and snappy.

Re: [sqlite] Any thoughts on optimizations for this query?

On 12 Nov 2011, at 3:43am, Josh Gibbs wrote: > We are struggling to find a way to rework this query in a way > that performs efficiently for large data sets. It's all about the indexes. > The goal is to find the top x most active senders of e-mails > within a date range. Do you frequently do