Re: [sqlite] Very Odd... where field1='' or field2='' is slow

On Fri, May 7, 2010 at 5:57 PM, Griggs, Donald wrote: > > Sqlite using a maximum of one index per table per select. > > In order to perform your "OR" select, it must scan every the table. > That information is obsolete as of SQLite version 3.6.8 (2009-01-12). All

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/07/2010 04:55 PM, Raeldor wrote: > That's interesting, I'll give that a shot. I've noticed in my dealings with > MS Sql server, that it is very bad at using compound indexes... I hope > sqlite is better! :) Put "explain query plan" in front of

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

That's interesting, I'll give that a shot. I've noticed in my dealings with MS Sql server, that it is very bad at using compound indexes... I hope sqlite is better! :) Simon Slavin-3 wrote: > > > On 7 May 2010, at 10:47pm, Raeldor wrote: > >> I have 2 individually indexed fields. I have a

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

On 7 May 2010, at 11:14pm, Raeldor wrote: > Seriously... it can only use one index per query? Sure. Because after using the first index it is no longer searching the table, it's searching a big collection of unindexed rows it got from the first search. There's no point in mindlessly making

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

On 7 May 2010, at 10:47pm, Raeldor wrote: > I have 2 individually indexed fields. I have a select statement... > > select * from table1 where field1='x' and field2='y' > > this takes about 10 sections, yet if I do... > > select * from table1 where field1='x' > > it's instant, and if i do...

Re: [sqlite] Changing file descriptor of database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/07/2010 01:43 PM, Pavel Ivanov wrote: > select() has limitation that it can be used > only with file descriptors less than 1024. This is rarely true and I only know of one crappy operating system that had that limitation 13 years ago. You can

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

Btw... does this 'one index' also include indexes used for joining? Thanks Ray Raeldor wrote: > > Hi, > > Seriously... it can only use one index per query? I tried your union > suggestion and it works well, thank you. I had no idea it was limited to > a single index. > > Thanks > Ray > >

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

Hi, Seriously... it can only use one index per query? I tried your union suggestion and it works well, thank you. I had no idea it was limited to a single index. Thanks Ray Griggs, Donald-3 wrote: > > Regarding: >select * from table1 where field1='x' or field2='y' > > > Hi, Ray,

Re: [sqlite] Very Odd... where field1='' or field2='' is slow

Regarding: select * from table1 where field1='x' or field2='y' Hi, Ray, I believe you sent two messages -- the first with "OR" and the second with "AND". Since your subject consistently says "OR" and your last email says "AND" I'll assume you meant "AND". Sqlite using a maximum of

[sqlite] Very Odd... where field1='' or field2='' is slow

Hi All, I have 2 individually indexed fields. I have a select statement... select * from table1 where field1='x' and field2='y' this takes about 10 sections, yet if I do... select * from table1 where field1='x' it's instant, and if i do... select * from table1 where field2='y' it's also

Re: [sqlite] Changing file descriptor of database file

> IMHO it would be better to switch to poll/epoll from select instead of > fighting file descriptor numbers. Sure, that's my opinion too. It's just some significant amount of work which looks like less prioritized at this moment than other application improvements. So I'm looking for some

Re: [sqlite] Changing file descriptor of database file

On Fri, May 7, 2010 at 4:43 PM, Pavel Ivanov wrote: > Hi, all! > > I'm writing a server application that uses SQLite database as a > storage. In fact for various reasons I use a lot of SQLite database > files at once. This application can serve hundreds of clients spread >

Re: [sqlite] Group by optimizer

> The documentation ought to say somewhere that the value of any column in an > aggregate query that is not part of the GROUP BY clause and which is not an > argument to an aggregate function is undefined.  Does it not say so > already? Looks like it doesn't. Searching on the site only this vague

[sqlite] Changing file descriptor of database file

Hi, all! I'm writing a server application that uses SQLite database as a storage. In fact for various reasons I use a lot of SQLite database files at once. This application can serve hundreds of clients spread all over the network. Communication with those clients goes over non-blocking sockets

Re: [sqlite] Group by optimizer

On Fri, May 7, 2010 at 3:42 PM, Pavel Ivanov wrote: > > Absent the min statement, and no other constraints, SQLite should take > > the first item and call it quits. > > No, it shouldn't. As I said without min() it's invalid SQL and SQLite > has the right to do whatever it

Re: [sqlite] Group by optimizer

> Absent the min statement, and no other constraints, SQLite should take > the first item and call it quits. No, it shouldn't. As I said without min() it's invalid SQL and SQLite has the right to do whatever it wants to. But of course I'd better see it returning error and not executing such SQL

Re: [sqlite] Group by optimizer

Yes, I have added the min and things work fine. select 'Start2';select min(c1),c2 from T0 group by c2 limit 1; Absent the min statement, and no other constraints, SQLite should take the first item and call it quits. select 'Start1';select * from T0 group by c2 limit 1; The second example

Re: [sqlite] Group by optimizer

On 5/7/10, Pavel Ivanov wrote: > Sorry, I can hardly understand what you are trying to say but want to > point out one error: > >> sqlite> select 'Start';select * from T0 group by c2; > > This is incorrect SQL. SQLite silently accepts it but its behavior is > undefined in such

Re: [sqlite] Group by optimizer

Sorry, I can hardly understand what you are trying to say but want to point out one error: > sqlite> select 'Start';select * from T0 group by c2; This is incorrect SQL. SQLite silently accepts it but its behavior is undefined in such situation. Any other database wouldn't execute such SQL. If

[sqlite] Group by optimizer

In the following code I try select by group on one column. I am assuming the query optimizer will figure to stop gathering column 1s right away because there are no other selection constraints. Yet in the example, sql still looks through the entire table for additional column ones as one can see

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

> To Pavel: My application reads the column types out in order to pretty > print the values - as mentioned by Tom - but also to generate a dialog > for entering new data (of course combined with INSTEAD OF TRIGGERs). So as I see it: you have some universal code for displaying and inputing data.

Re: [sqlite] Behaviour of tables with same name

On Fri, May 07, 2010 at 07:06:52AM -0700, a1rex scratched on the wall: > Since we are at this topic let me ask the question: Are the table > names case insensitive? $ ./sqlite3 SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table T

Re: [sqlite] Behaviour of tables with same name

> Since we are at this topic let me ask the question: Are the table names > case insensitive? Yes, I believe they are: as are all identifiers. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Behaviour of tables with same name

Since we are at this topic let me ask the question: Are the table names case insensitive? From: Andy Gibbs To: sqlite-users@sqlite.org Sent: Fri, May 7, 2010 8:00:44 AM Subject: Re: [sqlite] Behaviour of tables with same name > I

Re: [sqlite] Behaviour of tables with same name

> I think the bigger issue is that you probably shouldn't rely on > automatic resolution of names. ... If you're using multiple > databases-- even just temp and main-- the best solution is to > just qualify as much as you can. This is sound advice. Thank you, also, for the clarification of

Re: [sqlite] Behaviour of tables with same name

On Fri, May 07, 2010 at 09:43:13AM +0200, Andy Gibbs scratched on the wall: > Hi, > > I have tracked down a bug in some other software where two tables > have the same name, where it seems that the one table was masking the other. > What happens is that the table 't' in the main connection

Re: [sqlite] Advantages of WAL mode?

On Fri, May 7, 2010 at 6:39 AM, BFreeNews.com wrote: > Hi, > > I see lots of activity related to WAL mode in the Sqlite timeline. > > What are going to be the features/advantages of PRAGMA > journal_mode=WAL mode for Sqlite? > Documentation on WAL is still very incomplete.

Re: [sqlite] Returning column to default

Hi, I had a free couple of minutes, so I went in and added the functionality to sqlite since it was so straight-forward, so that it is now possible to do the following... UPDATE tab SET col = DEFAULT I chose this syntax since it is the syntax used by MS SQL Server and MySQL (and maybe

[sqlite] Advantages of WAL mode?

Hi, I see lots of activity related to WAL mode in the Sqlite timeline. What are going to be the features/advantages of PRAGMA journal_mode=WAL mode for Sqlite? Is there going to be better concurrency on write transactions? Less locking on database writes?... Thank you! :-) Jochi Martínez

[sqlite] A few simple parsers and search utilities for FTS3

This is the simple and extensible system which is providing Unix-way for creating the full-text search system by many types of documents for home or corporate usage. I'm sorry but it is documented only on Russian language. The project is released some times ago but I don't have enought free time

Re: [sqlite] Id ranges to be used with joins

> I have a table with two fields defining range of ids of another table, > > IdFrom and IdTo. I would like to use this information (multiply rows as > a > > SELECT result of this IdFrom and IdTo) in joins. > > Something like this? > > select * from MyTable join Ranges > on (MyTable.id between

Re: [sqlite] Id ranges to be used with joins

Max Vlasov wrote: > I doubt this is sqlite specific question, maybe sql in general. > I have a table with two fields defining range of ids of another table, > IdFrom and IdTo. I would like to use this information (multiply rows as a > SELECT result of this IdFrom and IdTo) in joins. Something

[sqlite] Id ranges to be used with joins

Hi, I doubt this is sqlite specific question, maybe sql in general. I have a table with two fields defining range of ids of another table, IdFrom and IdTo. I would like to use this information (multiply rows as a SELECT result of this IdFrom and IdTo) in joins. So ideally it would be great to find

[sqlite] Behaviour of tables with same name

Hi, I have tracked down a bug in some other software where two tables have the same name, where it seems that the one table was masking the other. I can demonstrate simply with the following (on v3.6.23.1): create table t(i); insert into t values (1); attach database "other.db" as other;