Re: [sqlite] ORDER BY Does not work

[mike cariotoglou]

yes, I am sure. I tested with 3.1.3, with the same results.

> 
> I'll eventually get to this.  If Dan's fix is correct, though,
> the problem was introduced after 3.2.2.  Are you sure you are
> not using code out of CVS?
> 

Re: [sqlite] Index and General Optimization Question

[Kurt Welgehausen]

Currently, indices are not used to look up either term
in an OR expression.

See .

You could build 2 separate 5-column indices and use a
union.

Regards

[sqlite] column name inconsistency...

[Preston & Chrystie]

I know there was a lot of talk about columns names a while back and i
don't want to start it all up again, but i have noticed some things in
the 3.2.2 release that seem a little odd to me.

if i write a query as follows:

select a.id, b.stuff
from a inner join b on a.id=b.id;

The column names come back as i expected they would: id, stuff.

If however, i create the following view:

create view aview as
select a.id, b.stuff
from a inner join b on a.id=b.id;

The column names don't come back as i expected they would instead they
are: a.id, b.stuff. I would have expected that to return the same
thing as the query. Obviously not a big deal to work around.. but
thought it was odd.

The same sort of thing happens on an in-line view or subselect. Take
the fictitious example below:

select a.id, tmpb.stuff
from a inner join
(
 select b.id, b.stuff
from b
) as tmpb
on tmpb.id=a.id;

The work-around for this is of course to explicitly alias your columns
to the names you were expecting, but it is probably worth changeing if
only to make it more intuitive and consistent.

thanks
--preston

Re: [sqlite] ORDER BY Does not work

[D. Richard Hipp]

On Fri, 2005-08-12 at 12:56 +0300, mike cariotoglou wrote:
> I have found a bug in the way sqlite treats ORDER BY clauses.
>   select kinapo.ekey, kinapo.polhths
>   from eidh inner join kinapo on eidh.ekey=kinapo.ekey
>   order by eidh.ekey, kinapo.polhths
>   ==
> 

I'll eventually get to this.  If Dan's fix is correct, though,
the problem was introduced after 3.2.2.  Are you sure you are
not using code out of CVS?

In any event, adding a unary "+" operator to the first term
of the ORDER BY clause will likely work around the problem
by defeating the optimizer:

ORDER BY +eidh.ekey, kinapo.polhths
 ^--  unary "+" here

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Index and General Optimization Question

[D. Richard Hipp]

On Fri, 2005-08-12 at 11:18 -0400, John Sample wrote:
> NAME='char' and TYPE='char' DIRP='char' DIRS='char' and (ZIPL =int OR 
> ZIPR=int)
> 
> Now to the question:
> Can/will a multicolumn index (ZIPL,ZIPR) be used for an OR clause?

Version 3.2.2 will not optimize the above.  But the latest code
in CVS will.  3.2.3 will probably be released relatively soon.

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Index and General Optimization Question

[Steve O'Hara]

Why don't you create and a very small version of the database, create the
indices and use the EXPLAIN command to see what it does for your queries.

Steve

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
rg]On Behalf Of John Sample
Sent: 12 August 2005 16:18
To: sqlite-users@sqlite.org
Subject: [sqlite] Index and General Optimization Question


Hello all.
I'm a new Sqlite user porting a large db for an open source project
from SQL Server.

I have a question regarding whether an index makes sense or will be
used for a certain scenario. Since the table in question is so large
it takes up to 8 hours for me to create new indexes, so I'd like to
see if I can get a best guess before trying.

A little background info:
This is for a GIS system. The Sqlite database is 2+ gigs at the moment.
The table in question has 17,000,000 rows and is performing well so far.
It has a multicolumn index on 4 columns which are always part of the
queries.

NAME TYPE DIRP DIRS

There are 2 more columns that make up the rest of the select clause,
but it is an OR statement. A pseudo select clause would look like
this:

NAME='char' and TYPE='char' DIRP='char' DIRS='char' and (ZIPL =int OR
ZIPR=int)

Now to the question:
Can/will a multicolumn index (ZIPL,ZIPR) be used for an OR clause?
If so, what if it was part of the other multicolumn index?

Thanks for any guidance!

[sqlite] Index and General Optimization Question

[John Sample]

Hello all.
I'm a new Sqlite user porting a large db for an open source project
from SQL Server.

I have a question regarding whether an index makes sense or will be
used for a certain scenario. Since the table in question is so large
it takes up to 8 hours for me to create new indexes, so I'd like to
see if I can get a best guess before trying.

A little background info:
This is for a GIS system. The Sqlite database is 2+ gigs at the moment.
The table in question has 17,000,000 rows and is performing well so far.
It has a multicolumn index on 4 columns which are always part of the queries.

NAME TYPE DIRP DIRS

There are 2 more columns that make up the rest of the select clause,
but it is an OR statement. A pseudo select clause would look like
this:

NAME='char' and TYPE='char' DIRP='char' DIRS='char' and (ZIPL =int OR ZIPR=int)

Now to the question:
Can/will a multicolumn index (ZIPL,ZIPR) be used for an OR clause?
If so, what if it was part of the other multicolumn index?

Thanks for any guidance!

Re: [sqlite] query 31 days

[Jakub Adamek]

Perhaps you could create a table with the results (e.g. columns 
yearmonth, date, hasrecord=0|1) and create triggers for the main table 
so that if the records change, the triggers change the value in the 
result table.


Jakub

Tom Deblauwe wrote:

Hello,

I'm using SQLite 2.8.16, on an embedded system, and I have a question.
I have a database with one table, containing all my records.  Each has a
field 'yearmonth' which contains for example '200508', and also a field
'date', which indicates the day of that month.  I want now to make an
overview of a month and see which days contain a record.  I've created a
combined index on 'yearmonth' and 'date'.  My query to do this is:
'select distinct date from mytable where yearmonth = 200508;'  This
takes about 50 seconds, which is really slow to show my users(it is
displayed on a webpage).

I noticed that this takes a long time, because I guess almost all
records are checked.  My other idea was to do 31 queries(one for every
day), like this: 'select ID from mytable where yearmonth = 200508 and
date = 1 limit 1;'.  This second method does not give me anymore speed
improvement.

for example:

 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ...
 ^   ^   ^   ^

the days marked with '^' contain records.  To check with the second
method for example day 7, it takes 2.6 seconds.  Day 4 takes less, and
from day 3 and lower, it is reacting immediately(like 40 ms).  Day 8 and
higher takes about 1 second each.

When I do a random query om my database with select, and want to return
10 results for example, it takes 2.8 seconds.  So getting 1 result, or
getting 10 results only differs about 0.2 seconds.  Is there anything I
can do so things go faster?  Maybe I should maintain a separate table
with the monthoverview, but if it is possible to do some other solution
it would be nicer ;-).

Thanks for your time,
kind regards,
Tom,






--

Jakub Adamek
Programmer
Telematix Software, a.s.
Karoliny Svetle 12, Praha 1
http://www.dynavix.com
Tel: +420 608 247197
Office: +420 224233470

[sqlite] Re: query 31 days

[Tom Deblauwe]

Ramon wrote:

Have you tried putting the yearmonth in 2 different columns, not sure if it 
will be
any faster, but I think it will, due to being a lot of them repeated.
Check you bandwidth and see if it's enough for your web users to access this 
query
remotely.
Peace



I will try this, thanx for the tip.

Kind regards,
Tom,

Re: [sqlite] Finding max of each group?

[Colin Fleming]

Thanks for the advice, guys. After fiddling around with it a bit, I
went for the age-old solution of changing the data and the
requirements so it wasn't necessary :)

Thanks,
Colin


On 10/08/05, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
> > select conversation_id, count(*), max(unread), max(updated_on)
> > from messages
> > where conversation_id in ()
> > group by conversation_id;
> >
> > I use max(updated_on) to get the date of the most recent message in
> > the conversation. Is there a way to return the ID of this message?
> 
> Assuming that the messages table has a column called msg_id,
> 
>   select t1.conversation_id, t2.c, t2.mun, t2.mup, t1.msg_id
>   from messages t1,
>(select conversation_id cid, count(*) c,
>max(unread) mun, max(updated_on) mup
> from messages
> where cid in ()
> group by cid) t2
>   where t1.conversation_id = t2.cid and t1.updated_on = t2.mup
> 
> 
> Regards
>

Re: [sqlite] ORDER BY Does not work

[Dan Kennedy]

In the file where.c, near the bottom of the function 
isSortingIndex there is a line:

if( j>=nTerm || (i>=pIdx->nColumn && pIdx->onError!=OE_None) ){

Comment out the second part of the expression as follows:

if( j>=nTerm /* || (i>=pIdx->nColumn && pIdx->onError!=OE_None) */){

At the moment I'm thinking that second part should just be 
removed, but I'm not completely sure - maybe it should be replaced
with something else. At any rate it's just an optimization, so it
is safe to comment it out.



--- mike cariotoglou <[EMAIL PROTECTED]> wrote:

> I have found a bug in the way sqlite treats ORDER BY clauses.
> to reproduce the bug, run this script against an empty database (memory db
> would do):
> 
>   ===
>   CREATE TABLE Eidh (
> ekey VARCHAR(12) NOT NULL,
> perigrafh VARCHAR(30) NOT NULL,
> PRIMARY KEY(ekey));
> 
> 
>   INSERT INTO Eidh VALUES('0001','bla');
> 
>   CREATE TABLE KinApo (
> ekey VARCHAR(12) NOT NULL ,
> date DATE,
> polhths INTEGER);
> 
>   INSERT INTO KinApo VALUES('0001',38353,40);
>   INSERT INTO KinApo VALUES('0001',38353,30);
>   INSERT INTO KinApo VALUES('0001',38353,20);
> 
>   select kinapo.ekey, kinapo.polhths
>   from eidh inner join kinapo on eidh.ekey=kinapo.ekey
>   order by eidh.ekey, kinapo.polhths
>   ==
> 
> versin 3.2.2 of sqlite gives the following result set, which is unordered:
> 
>   0001 40
>   0001 30
>   0001 20
> 
> also, EXPLAIN shows that no sorting is taking place.
> 
> This is a SERIOUS malfunction!
> can you pls verify this in other environments (I am using dll 3.2.2, locally
> compiled),
> and do something about it.
> 
> 
> 
> 





__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

Re: [sqlite] ORDER BY Does not work

[Markus Weissmann]

I can verify this bug on Darwin/Mac OS X with both Apples 3.1.3 and  
DarwinPorts 3.2.2;



-Markus

On 12.08.2005, at 11:56, mike cariotoglou wrote:


I have found a bug in the way sqlite treats ORDER BY clauses.
to reproduce the bug, run this script against an empty database  
(memory db

would do):

  ===
  CREATE TABLE Eidh (
ekey VARCHAR(12) NOT NULL,
perigrafh VARCHAR(30) NOT NULL,
PRIMARY KEY(ekey));


  INSERT INTO Eidh VALUES('0001','bla');

  CREATE TABLE KinApo (
ekey VARCHAR(12) NOT NULL ,
date DATE,
polhths INTEGER);

  INSERT INTO KinApo VALUES('0001',38353,40);
  INSERT INTO KinApo VALUES('0001',38353,30);
  INSERT INTO KinApo VALUES('0001',38353,20);

  select kinapo.ekey, kinapo.polhths
  from eidh inner join kinapo on eidh.ekey=kinapo.ekey
  order by eidh.ekey, kinapo.polhths
  ==

versin 3.2.2 of sqlite gives the following result set, which is  
unordered:


  0001 40
  0001 30
  0001 20

also, EXPLAIN shows that no sorting is taking place.

This is a SERIOUS malfunction!
can you pls verify this in other environments (I am using dll  
3.2.2, locally

compiled),
and do something about it.



---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

[sqlite] ORDER BY Does not work

[mike cariotoglou]

I have found a bug in the way sqlite treats ORDER BY clauses.
to reproduce the bug, run this script against an empty database (memory db
would do):

  ===
  CREATE TABLE Eidh (
ekey VARCHAR(12) NOT NULL,
perigrafh VARCHAR(30) NOT NULL,
PRIMARY KEY(ekey));


  INSERT INTO Eidh VALUES('0001','bla');

  CREATE TABLE KinApo (
ekey VARCHAR(12) NOT NULL ,
date DATE,
polhths INTEGER);

  INSERT INTO KinApo VALUES('0001',38353,40);
  INSERT INTO KinApo VALUES('0001',38353,30);
  INSERT INTO KinApo VALUES('0001',38353,20);

  select kinapo.ekey, kinapo.polhths
  from eidh inner join kinapo on eidh.ekey=kinapo.ekey
  order by eidh.ekey, kinapo.polhths
  ==

versin 3.2.2 of sqlite gives the following result set, which is unordered:

  0001 40
  0001 30
  0001 20

also, EXPLAIN shows that no sorting is taking place.

This is a SERIOUS malfunction!
can you pls verify this in other environments (I am using dll 3.2.2, locally
compiled),
and do something about it.

[sqlite] query 31 days

[Tom Deblauwe]

Hello,

I'm using SQLite 2.8.16, on an embedded system, and I have a question.
I have a database with one table, containing all my records.  Each has a
field 'yearmonth' which contains for example '200508', and also a field
'date', which indicates the day of that month.  I want now to make an
overview of a month and see which days contain a record.  I've created a
combined index on 'yearmonth' and 'date'.  My query to do this is:
'select distinct date from mytable where yearmonth = 200508;'  This
takes about 50 seconds, which is really slow to show my users(it is
displayed on a webpage).

I noticed that this takes a long time, because I guess almost all
records are checked.  My other idea was to do 31 queries(one for every
day), like this: 'select ID from mytable where yearmonth = 200508 and
date = 1 limit 1;'.  This second method does not give me anymore speed
improvement.

for example:

 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ...
 ^   ^   ^   ^

the days marked with '^' contain records.  To check with the second
method for example day 7, it takes 2.6 seconds.  Day 4 takes less, and
from day 3 and lower, it is reacting immediately(like 40 ms).  Day 8 and
higher takes about 1 second each.

When I do a random query om my database with select, and want to return
10 results for example, it takes 2.8 seconds.  So getting 1 result, or
getting 10 results only differs about 0.2 seconds.  Is there anything I
can do so things go faster?  Maybe I should maintain a separate table
with the monthoverview, but if it is possible to do some other solution
it would be nicer ;-).

Thanks for your time,
kind regards,
Tom,