I have investigated more.
It seems that the performance problem is simply
related to the query:
SELECT MAX(DTFROM) AS MAXDT, IDCATALOGUE FROM
CRONOCATALOGUES GROUP BY IDCATALOGUE
which takes at first execution 9 seconds in access and
30 seconds in sqlite, and it takes, after first
execution (OS c
I'm no expert, but I believe I recall reading on one of the SQLite
docs that the performance of Joins can be a bit slower than on other
DB systems. Seeing as you have a couple of tables being joined, this
could be the case. I also think I recall reading some posts in this
list about people changing
Mitchell Vincent wrote:
> SELECT customer_id FROM customers WHERE cust_balance != (select
> coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND
> invoice.customer_id = customers.customer_id)
>
> The above query is used to determine if any stored balances are out of
> date. It work
Correcting a minor nit:
Igor Tandetnik wrote:
> Magnus Manske <[EMAIL PROTECTED]>
> wrote:
> The condition could be written a bit more laconically:
>
> pos1 BETWEEN 1 AND 100
>
>> On my table (ca. 180K entries) this takes about 10 seconds for the
>> above, which returns all rows.
>
> Since
Magnus Manske <[EMAIL PROTECTED]>
wrote:
> I've found the strangest issue today. I have a sqlite3 database that
> contains the following table:
>
> CREATE TABLE MAL9_single ( read_name VARCHAR[32], pos1 INTEGER, seq1
> VARCHAR[64] );
> CREATE INDEX MAL9_sin_index ON MAL9_single ( pos1 );
>
> From P
"Jawahar Nayak" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
>I am sqllite user. I am using sqlite3-3.5.9. I am inserting some
> unicode data in utf-8 encoding format in database. When I tried the
> data back from the database it seems that data is being converted to
> some other
Igor Tandetnik wrote:
>
> "sqlite" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
>> We are using SQLite for our application development which would be
>> deployed in a pocket pc.Here we are using a inner join query which
>> takes different time during different executions
>
>
"sqlite" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> We are using SQLite for our application development which would be
> deployed in a pocket pc.Here we are using a inner join query which
> takes different time during different executions
What exactly does this mean? Are you ru
What if you took a slightly different tack?
CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate
null=unknown
);
And then periodically run this
"Doug" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Thank you Igor. The GROUP BY was the secret (I was trying to do a
> GROUP BY on the outer select, but it wasn't quite cutting it).
>
> GROUP BY is very powerful, but I notice it has a performance cost.
> Is there a way to use an
f Of Igor Tandetnik
> Sent: Tuesday, May 27, 2008 12:24 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query help?
>
> Doug <[EMAIL PROTECTED]> wrote:
> > SELECT ProcessID, count(1), sum(BytesProcessed)
> > FROM FinishedWork
> > WHERE Eve
Doug <[EMAIL PROTECTED]> wrote:
> SELECT ProcessID, count(1), sum(BytesProcessed)
> FROM FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
>
> Unfortunately when a file is processed twice, it's counted twice (ie
> added into the sum twice) and I need to show only uniq
Hi,
Bharath Booshan L wrote:
> Yeah!! I got it right this time.
>
> Select count(*) from ( select DISTINCT Name from TableA);
>
>
> But what's not getting into my mind is the difference b/w the following two
> queries:
>
> Select count( Name) from TableA -- works fine
>
> Select count(DISTIN
Hi,
What is the error?
It seems to me that both of these should work:
select count(DISTINCT Name) from TableA
select count(DISTINCT Name) as nameCount from TableA
Regards,
Eugene Wee
Bharath Booshan L wrote:
> Thanks for your quick response,
>
>> select count(DISTINCT Name) nameCount from Tab
Yeah!! I got it right this time.
Select count(*) from ( select DISTINCT Name from TableA);
But what's not getting into my mind is the difference b/w the following two
queries:
Select count( Name) from TableA -- works fine
Select count(DISTINCT Name) from TableA -- doesn't work, Any reason?
Thanks for your quick response,
> select count(DISTINCT Name) nameCount from TableA
This is not working :(. I am getting the same error.
--
Bharath
On 2/27/08 12:38 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> Try
> select count(DISTINCT Name) nameCount from TableA
>
> Shibu Narayan
Try
select count(DISTINCT Name) nameCount from TableA
Shibu Narayanan
Consultant, PrimeSourcing Division, Investment Banking Group
Tel.Office: 91-80-2208-6270 or 91-80-6659-6270
e-mail: [EMAIL PROTECTED]
The answer is 42.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROT
I am really thankful to all of you who replied my query and helped solve my
problem. Again thank you very much.
Best Regards,
Yasir Nisar
-
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
On Wed, Jan 23, 2008 at 10:24:53PM -0800, Scott Hess wrote:
> Seems to me that GLOB is a poor substitute for REGEXP. At the shell
If, as I suspect, many more users can enter simple globs than can enter
simple regexps, then providing a GLOB operator and function in SQLite is
very useful indeed. O
On Wed, Jan 23, 2008 at 3:23 PM, James Dennett
<[EMAIL PROTECTED]> wrote:
> The POSIX/Single Unix Spec documentation for fnmatch might be a good
> source, but I agree with the idea that SQLite should just document what
> it does rather than assuming that there's a universal standard for
> globb
James Dennett wrote:
The POSIX/Single Unix Spec documentation for fnmatch might be a good
source, but I agree with the idea that SQLite should just document what
it does rather than assuming that there's a universal standard for
globbing.
I think the most direct documentation I have found so
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 23, 2008 3:08 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query problem
>
> James Dennett wrote:
> >
> > Square brackets don't "escap
James Dennett wrote:
Square brackets don't "escape" thing that way: [[] is a character class
containing only the character '['. [][], however, is a character class
containing two characters. The special rule is that the first character
after the opening '[' is part of the class even if it's a
[EMAIL PROTECTED] wrote:
Experiments using bash indicate that either ^ or ! is accepted
as the negation of a character set. Hence,
ls -d [^tu]*
ls -d [!tu]*
both return the same thing - a list of all files and directories
in the current directory whose names do not begin with "t" or "
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 23, 2008 2:22 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query problem
>
> [EMAIL PROTECTED] wrote:
> >
> > You
> > can escape characters
[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote:
This appears to be slightly different than normal *nix globbing since
SQLite uses '^' rather than '!' for the set inversion (if my reading of
the source is correct).
GLOB is suppose to exactly mimic Unix, except that SQLite does not
break patter
[EMAIL PROTECTED] wrote:
You
can escape characters using [..]. To match a * anywhere in a string,
for example:
x GLOB '*[*]*'
The [..] pattern must contain at least one internal character. So
to match a "]" you can use the pattern
x GLOB '*[]]*'
So to match the OP's original string he would
On Wed, Jan 23, 2008 at 09:38:06PM +, [EMAIL PROTECTED] wrote:
> Experiments using bash indicate that either ^ or ! is accepted
> as the negation of a character set. Hence,
>
> [...]
>
> Anybody have an old Bourne shell around? An authentic C-shell?
> What do they do?
The Bourne Shell uses
DRH wrote:
>
> Experiments using bash indicate that either ^ or ! is accepted
> as the negation of a character set. Hence,
>
> ls -d [^tu]*
> ls -d [!tu]*
>
> both return the same thing - a list of all files and directories
> in the current directory whose names do not begin with "t" or
[EMAIL PROTECTED] wrote:
>
> > This appears to be slightly different than normal *nix globbing since
> > SQLite uses '^' rather than '!' for the set inversion (if my reading of
> > the source is correct).
>
> GLOB is suppose to exactly mimic Unix, except that SQLite does not
> break pattern match
Dennis Cote <[EMAIL PROTECTED]> wrote:
>>
> SQLite seems to do the following:
>
> The glob syntax supports the following patterns:
> ? - matches any single character
> * - matches zero or more characters
> [seq] - matches any single character in seq
> [!seq] - matches any
Yasir Nisar wrote:
Hi,
Hope you will find this mail in the best of your health.
SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo = BackupItemTable.Bac
You need single quotes for text literals.
On Jan 23, 2008 6:15 AM, Yasir Nisar <[EMAIL PROTECTED]> wrote:
> SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE
> lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND
> BackupItemTable.BKItemSize > -1 AND BackupTable.Backup
arbalest06 wrote:
i already got it working..
I'm glad to hear that.
however, is still have the free(): invalid pointer problem in my
sqlite3_close..this is my code for my close api:
if( GDBM_Db_p != NULL )
{
printf( "FDBM_Close: GDBM_Db_p is not NULL\n" );
/* closes t
i already got it working..the only problem was that my condition ( char * ) =
"id=1"..i got it working when i made it to "id = 1"..spaces did that trouble
to my code..however, is still have the free(): invalid pointer problem in my
sqlite3_close..this is my code for my close api:
if( GDBM_Db_
arbalest06 wrote:
im implementing a C program that uses the sqlite as my database..im using a
global database pointer because im creating my apis for open and close
database..my problem is that when i put a select query, the return value is
1..i copied my query to the console of sqlite3 and it wo
Igor Tandetnik wrote:
>
> Reordering LEFT JOIN changes the meaning of the statement. You don't
> want your DBMS to do that to you behind your back. Make sure you know
> what you are doing, and that the reordered statement still does what
> it's supposed to do. Again, (A LEFT JOIN B) produces
OK, that fixes it on the commandline. Now to figure out how to link
against the new version...
Joe Wilson wrote:
Time to upgrade.
--- Christopher Backhouse <[EMAIL PROTECTED]> wrote:
I repeated exactly what you have there and confirm it doesn't work for me.
[EMAIL PROTECTED]:/tmp$ sqlite3 te
Workaround for 3.3.13:
select distinct bar, baz as z from foo where bar in ((
select bar from foo where baz==z order by length(bar) desc limit 1
));
It's just different enough to prevent sqlite 3.3.13 from choking on
the "order by" clause in the "in" subquery.
--- Christopher Backhouse <[EM
Time to upgrade.
--- Christopher Backhouse <[EMAIL PROTECTED]> wrote:
> I repeated exactly what you have there and confirm it doesn't work for me.
>
> [EMAIL PROTECTED]:/tmp$ sqlite3 testing
> SQLite version 3.3.13
> Enter ".help" for instructions
> sqlite> CREATE TABLE foo(bar text, baz text);
>
I repeated exactly what you have there and confirm it doesn't work for me.
[EMAIL PROTECTED]:/tmp$ sqlite3 testing
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> CREATE TABLE foo(bar text, baz text);
sqlite> INSERT INTO "foo" VALUES('123','345');
sqlite> INSERT INTO "foo" VALUES('12
--- Christopher Backhouse <[EMAIL PROTECTED]> wrote:
> I have a table:
> create table foo(bar text,baz text);
>
> I want to produce a list of all bar's paired with the longest baz they
> are ever associated with. I came up with the following query:
>
> select distinct bar,baz as z from foo where
Tue, 20 Mar 2007 14:15:52 - Ajay Arora wrote:
> [snip]
>And it gives me an error saying "e.extract_sequence" does not exist".
DROP TABLE IF EXISTS extract_master;
CREATE TABLE extract_master (
extract_sequence INTEGER PRIMARY KEY,
file_locationTEXT,
active
On 2/19/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote:
The application is an experimental anti-Spam testbed exploring the use
of reputation through proof for puzzles and user interaction. The list
of things I'm tracking is fairly significant (for me) and I'm trying to
pull things together.
On 2/19/07, Eric S. Johansson <[EMAIL PROTECTED]> wrote:
Joe Wilson wrote:
>> 2) can I use it for a message store and have it run faster than a 1
>> message per file system?
>
> What does that mean? Is "file system" a unit of time?
ack! bad sentence construction... comparing a system storing 1
Joe Wilson wrote:
2) can I use it for a message store and have it run faster than a 1
message per file system?
What does that mean? Is "file system" a unit of time?
ack! bad sentence construction... comparing a system storing 1 message
per file (and thousands of messages total) vrs one sto
> 1) need to know if sqlite can help me cut down on the number of little db's
Sure.
> 2) can I use it for a message store and have it run faster than a 1
> message per file system?
What does that mean? Is "file system" a unit of time?
You can store messages in BLOBs or as TEXT in an SQLite ta
Eric S. Johansson wrote:
Again, I do most sincerely apologize for size of this request. I've
spent 30 years avoiding databases and I guess this is payback. :-) Any
advice, good online tutorials on SQL, or feedback would be valued.
urk. I hate it when I forget to ask for what I need after in
PROTECTED] [mailto:[EMAIL PROTECTED]
Enviado el: martes, 19 de diciembre de 2006 16:48
Para: sqlite-users@sqlite.org
Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY
Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jesús López wrote:
> > Given the table:
> >
&
: sqlite-users@sqlite.org
Asunto: Re: [sqlite] Query Optimizer and INTEGER PRIMARY KEY
Jesús López wrote:
> Given the table:
>
> CREATE TABLE Posts (
> PostID INTEGER PRIMARY KEY,
> CategoryID INT NOT NULL
> )
>
> And the index:
>
> CREATE UNIQUE INDEX IX_Post
[EMAIL PROTECTED] wrote:
In SQLite, every index has the INTEGER PRIMARY KEY as an implied
final column. So an index on
Post(CatagoryID, PostID)
Is really an index like this:
Post(CategoryID, PostID, PostID)
In other words, the extra PostID column is redundant. If you
omit it all t
Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jesús López wrote:
> > Given the table:
> >
> > CREATE TABLE Posts (
> > PostID INTEGER PRIMARY KEY,
> > CategoryID INT NOT NULL
> > )
> >
> > And the index:
> >
> > CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID)
> >
>
> Your create index stateme
Jesús López wrote:
Given the table:
CREATE TABLE Posts (
PostID INTEGER PRIMARY KEY,
CategoryID INT NOT NULL
)
And the index:
CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID)
The query:
SELECT CategoryID, PostID
FROM Posts
ORDER BY CategoryID
LIMIT 10
Takes about 15 ms to complete.
Pusedo code:
set s.sql = 'select a from mytable where (b=0)';
if got.more then
set s.sql = s.sql + ' and ' + s.userinput;
...
set s.sql = s.sql + ';';
> -Original Message-
> From: Lloyd [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 07, 2006 2:46 AM
> To: sqlite-users@sqlite.org
At 2:15 PM +0530 12/7/06, Lloyd wrote:
Hi,
I am generating queries dynamically from my program based on the input.
The primary part of the query is always
select a from mytable where (b=0) and
based on the user input I add more where clauses to my query like
select a from mytable where (b=0) and
Thanks, 0=0 works well enough for me. (This hint is what I wanted)
Thanks again,
Lloyd
On Wed, 2006-12-06 at 10:00 +0100, Mario Frasca wrote:
> Lloyd wrote:
>
> >select a from mytable where (b=0) and
> >
> >and this is a syntax error.
> >
> >
> you're not saying which language you're using.
Lloyd wrote:
select a from mytable where (b=0) and
and this is a syntax error.
you're not saying which language you're using.
in Python a common solution looks like this:
clauses = []
# add strings to the clauses list, like
clauses.append('(b=0)')
# join the parts using appropriate glue
wh
:[EMAIL PROTECTED]
Sent: Thursday, November 30, 2006 11:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query on database operations.
You might find that a version of Sqlite which omits the compiler might
fit your application. You get a smaller footprint, but at the cost of
having to use
costly. Hence I don't think we can omit indexes.
-Original Message-
From: Nicolas Williams [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 30, 2006 8:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query on database operations.
On Thu, Nov 30, 2006 at 08:03:00PM +0530, Ka
PROTECTED]
Sent: Thursday, November 30, 2006 11:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query on database operations.
You might find that a version of Sqlite which omits the compiler might
fit your application. You get a smaller footprint, but at the cost of
having to use pre
You might find that a version of Sqlite which omits the compiler might
fit your application. You get a smaller footprint, but at the cost of
having to use pre-compiled SQL, which means that you cannot dynamically
create SQL.
Kalyani Tummala wrote:
Hi,
This mail is a bit lengthy. I thank you
On Thu, Nov 30, 2006 at 08:03:00PM +0530, Kalyani Tummala wrote:
> With indexes on every column(searched), the following is the heap size
> for different database operations on a database with 100 records and 6
> tables with an avg of 10 to 15 fields each.
You can probably get by quite well withou
"Kalyani Tummala" <[EMAIL PROTECTED]> wrote:
>
> Heap memory is highly variant on size and number of records. I tried
> modifying the page size(SQLITE_DEFAULT_PAGE_SIZE and
> SQLITE_MAX_PAGE_SIZE ) in pager.h from 512 to 2048 but found no
> reduction in heap size.
>
Try changing SQLITE_DEFAULT_
[EMAIL PROTECTED] wrote:
>
> "SELECT * from database where STRING1 = 'x' && STRING2 = 'y' && STATE = 1
> && purpose = 'z'"
> "Create INDEX INDX on database( STRING1, STRING2, state, purpose)"
>
> i)
> If above index is present then the SELECT statement given,
> iterates to the all ro
On Wed, 15 Nov 2006 03:28:50 +, [EMAIL PROTECTED] wrote:
Jean-Paul Calderone <[EMAIL PROTECTED]> wrote:
I've hit a case where SQLite is selecting a suboptimal index for my query and I want to
do something to tell it to use a different index. I'm familiar with the "+"
trick to confuse the
Jean-Paul Calderone <[EMAIL PROTECTED]> wrote:
> I've hit a case where SQLite is selecting a suboptimal index for my query and
> I want to do something to tell it to use a different index. I'm familiar
> with the "+" trick to confuse the optimizer but I can't see how it would
> apply to this ca
Christian Smith <[EMAIL PROTECTED]> wrote:
>>
> Version 2 used a Red/Black balanced tree in :memory:, whereas version 3
> appears to have done away with this optimisation. I'm curious as to why?
>
It is simpler to support a single algorithm (b-trees) rather than
two (b-tree + red/black trees).
Manzoor Ilahi Tamimy uttered:
I found that Version 2.8 was much Faster than 3.6 using ":memory:", 30
Seconds and 60 seconds in case of 2.8 and 3.6 respectively.
can I use 2.8 in my project when i have a huge amount of data to handle.
Version 2 used a Red/Black balanced tree in :memory:, wher
Yes, this is exactly that i need.
thanks.
CiberCorreos®
Acercando las Nuevas Tecnologías a la población.
http://www.ciudadano.cu/
-
To unsubscribe, send email to [EMAIL
Hello,
Is it the "LIMIT" clause you need? If so, did you check the syntax page?
http://sqlite.org/lang_select.html
[LIMIT integer [( OFFSET | , ) integer]]
For example,
SELECT * FROM mytable LIMIT 100;
>From the above page:
--
The LIMIT clause places an upper bound on the n
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> David Champagne
> wrote:
> > If I have a table defined in the following manner
> >
> >CREATE TABLE License (Form varchar(256), Type int, NbOcc int)
> >
> > and then I execute a query
> >
> >SELECT * FROM License WHERE FORM = "form";
> >
> > I g
David Champagne wrote:
If I have a table defined in the following manner
CREATE TABLE License (Form varchar(256), Type int, NbOcc int)
and then I execute a query
SELECT * FROM License WHERE FORM = "form";
I get all rows returned, even though I only want the rows where the
column FORM
I would tend to avoid mixed case and never use all upper case to specify
anything (other than when defining a constant perhaps). Your use of
case between "FORM" and "Form" as example.
Also the use of the term "Type" for a fieldname (or variable) may be
stretching reserved words a bit. For th
Monday, September 25, 2006, 1:46:12 PM, David Champagne wrote:
> and then I execute a query
> SELECT * FROM License WHERE FORM = "form";
> I get all rows returned
Try:
SELECT * FROM License WHERE FORM = 'form';
e
--
Doug Currie
Londonderry, NH
-
On Wed, 9 Aug 2006 18:51:30 +0600, you wrote:
>hi All,
>
>I have to use SQLite for one of my project as ":memory:" db.
[snip]
>which PRAGMA statements can improve the query speed.
>the main queries are, INSERT and SELECT with joins.
>
>I have just tried the following PRAGMA
>
>sqlite3_e
Am 09.08.2006 um 05:51 schrieb Manzoor Ilahi Tamimy:
hi All,
I have to use SQLite for one of my project as ":memory:" db.
//
-
Can I get a better speed if I change or omit some macros.
I saw " http://www.sqlite.or
Kurt,
Thanks, the single primary index you suggested does give us the
desired results.
Steve
Kurt Welgehausen wrote:
Steve Green <[EMAIL PROTECTED]> wrote:
CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
...
CREATE INDEX ix_data_ut on data( u_id, utime );
Wouldn't a unique inde
Steve Green <[EMAIL PROTECTED]> wrote:
> the unary + operator in front of the two utime terms did cause the optimal
> index to be used... Can you explain why this works?
A unary + in front of a column name disqualifies the term from
consideration by the optimizer, and hence from use by indices.
Th
Steve Green <[EMAIL PROTECTED]> wrote:
> CREATE UNIQUE INDEX pk_data on data( utime, r_id, u_id );
> ...
> CREATE INDEX ix_data_ut on data( u_id, utime );
Wouldn't a unique index on (u_id, utime, r_id) get you
the pk constraint and better performance on your query
with no other index to confuse t
Richard,
Thanks for the reply. I did run ANALYZE and that didn't help. However,
the unary + operator in front of the two utime terms did cause the optimal
index to be used... Can you explain why this works?
I also realized that if I change the "where" clause to something like
where u_id >= 0 a
Steve Green <[EMAIL PROTECTED]> wrote:
[...]
>
> select u_id, sum( data1 ), sum( data2 )
> from data where utime >= 1146441600 and utime < 114912
> group by u_id
> order by sum( data1 ) desc
> limit 10
>
[...]
>
> So, is there a way that I can get sqlite to use the optimal index
> without ha
Ah.. okay - you did not mention tables t1 and t2
were in different database files. That is likely why
this case was never tested. Explicit cross joins
are the way to go - you know the data better than
the database.
Read about SQLite's CROSS JOIN logic here:
http://www.sqlite.org/cvstrac/wiki?p=
Ah.. okay - you did not mention tables t1 and t2
were in different database files. That is likely why
this case was never tested. Explicit cross joins
are the way to go - you know the data better than
the database.
Read about SQLite's CROSS JOIN logic here:
http://www.sqlite.org/cvstrac/wiki?p=
Interesting... I googled cross join and got the following definition:
"A cross join (or Cartesian Product join) will return a result table where
each row from the first table is combined with each row from the second
table. The number of rows in the result table is the product of the number
of
CROSS JOIN is an SQLite-specific thing to disable
table join optimization.
Please post the schema and indexes of yours tables
so that this bug may be corrected when CROSS JOIN is not used.
--- Steve Green <[EMAIL PROTECTED]> wrote:
> Interestingly, using
>
> from t1
> cross join t2
>
>
Interestingly, using
from t1
cross join t2
fixes the problem; using this, causes indices from both tables to be used...
addr opcode p1 p2 p3
-- -- -- -
37OpenRead0 4
38
maybe "t2 cross join t1" makes more sense given the
sizes of the tables.
--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> Hard to say what's the problem if you don't post the
> schema of the tables and the indexes, and provide some
> sample data.
>
> Perhaps there is a bug in the join optimizer.
>
Hard to say what's the problem if you don't post the
schema of the tables and the indexes, and provide some
sample data.
Perhaps there is a bug in the join optimizer.
Try using an explicit CROSS JOIN.
select t1.a, t1.b, t2.c, t2.d
from t1
cross join t2
where t1.x = t2.x and t1.a >
The performance is exactly the same after running analyze on both tables.
Steve
Joe Wilson wrote:
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is missing
Run an ANALYZE statement on your database and your
queries will be fast once again.
Perhaps SQLite should fall back to the old non-optimized
join behavior when the sqlite_stat1 table is missing.
This would cover 99% of the pre-SQLite3.2.3 legacy databases
out there where the queries have alread
Wilfried Mestdagh a écrit :
Hello,
I'm not sure if subject make sence (English is not my mothers tongue), I
try to explain:
I have a table and a field called 'Name' can have a lot of the same but
also different values. I want (if exists of course) to do a query where
I only get as result the ex
hat restricts the RIGHT hand table only.
Just a thought.
> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 20, 2005 12:07 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query problem
>
> I've changed my mind. I
sqlite-users@sqlite.org
|
| cc:
|
| Subject: - R
On Mon, 2005-09-19 at 15:19 -0400, D. Richard Hipp wrote:
> On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote:
> > hi all,
> >
> > I am porting a program from mysql to sqlite.
> > The following statement doesn't work correctly with sqlite though it does
> > with mysql.
> > SELECT COUNT(*) AS
On Mon, 2005-09-19 at 16:06 -0400, Kervin L. Pierre wrote:
> D. Richard Hipp wrote:
> > Hence, the result set contains no rows. A COUNT() of a empty result
> > set gives NULL.
>
> I thought per the last discussion on "Sum and NULL"
> that the count of an empty set would return zero.
>
You're ri
D. Richard Hipp wrote:
Hence, the result set contains no rows. A COUNT() of a empty result
set gives NULL.
I thought per the last discussion on "Sum and NULL"
that the count of an empty set would return zero.
Regards,
Kervin
On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote:
> hi all,
>
> I am porting a program from mysql to sqlite.
> The following statement doesn't work correctly with sqlite though it does
> with mysql.
> SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
> ttd_photos.kind=1 AND ttd_p
On Sep 19, 2005, at 12:36 PM, Alain Bertrand wrote:
hi all,
I am porting a program from mysql to sqlite.
The following statement doesn't work correctly with sqlite though it
does
with mysql.
SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON
ttd_photos.kind=1 AND ttd_photos.refId=t
Khamis Abuelkomboz wrote:
Hi
How many records contain your table? Your select command scans all the
table entries and I guess that the database uses the standard index.
try the following command to tell the db which index it should use:
select distinct date from mytable where yearmonth = '2
Hi
How many records contain your table? Your select command scans all the
table entries and I guess that the database uses the standard index. try
the following command to tell the db which index it should use:
select distinct date from mytable where yearmonth = '200508' order by date
Anothe
501 - 600 of 640 matches
Mail list logo
