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

[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] 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 *

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] 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

[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

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

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

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] 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] 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] 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

[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

[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

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

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] 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] 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] [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] 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

[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] [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

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] 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] 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] [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.

[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

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' > >>

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

[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

[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

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

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

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 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

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 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

[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] 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