[sqlite] 3.6.17 not checking for readline.h in configure script.

[Dr. David Kirkby]

This is with a Sun Ultra 80, running Solaris 10 update 7 with Sun Studio 
12 update 1.

drkir...@smudge:[~/sqlite-3.6.17] $ echo $CC
/opt/sunstudio12.1/bin/cc
drkir...@smudge:[~/sqlite-3.6.17] $ echo $CXX
/opt/sunstudio12.1/bin/CC


There is an issue where the configure script checks for readline

checking for readline... yes


then later we get error messages

/opt/sunstudio12.1/bin/cc -DPACKAGE_NAME=\"sqlite\" 
-DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.6.17\" 
-DPACKAGE_STRING=\"sqlite\ 3.6.17\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE=\"sqlite\" 
-DVERSION=\"3.6.17\" -D_FILE_OFFSET_BITS=64 -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_READLINE=1 
-I. -I.-DSQLITE_THREADSAFE=1  -g -c shell.c
"shell.c", line 44: cannot find include file: 
"shell.c", line 45: cannot find include file: 
"shell.c", line 1165: warning: implicit function declaration: readline
"shell.c", line 1165: warning: improper pointer/integer combination: op "="
"shell.c", line 1167: warning: implicit function declaration: add_history
"shell.c", line 3166: warning: implicit function declaration: read_history
"shell.c", line 3170: warning: implicit function declaration: stifle_history
"shell.c", line 3171: warning: implicit function declaration: write_history
cc: acomp failed for shell.c
make: *** [shell.o] Error 1


So it looks to me that there should be a more thorough check of exactly 
what is needed with readline. Perhaps a check should be done of the 
header files too in the configure script, before deciding to install 
readline support.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] debugging sqlite with gdb

[Mohammad Reaz Uddin]

Hey,
I am running sqlite-3.6.16. I am getting segmentation fault and so trying
to run sqlite with gdb. But I am getting following error-

/sqlite-3.6.16/sqlite3": not in executable format: File format not recognized

I saw the make file. I think it's compiled with debugging option(-g).
what to do?

--Reaz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Exists Query

[Simon Slavin]

On 15 Aug 2009, at 3:57pm, Andrew Gatt wrote:

> Simon Slavin wrote:
>> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:
>>
>>> but by my scheme you can't be sure if the list_1 table exists in
>>> database2,
>>
>> Add a column to your 'lists' tables which says which database they're
>> part of.  So every row of the database will have the same value.
>>
>
> Unfortunately the database names change (they are assigned as and when
> the storage is attached)

Wait ... the first time the storage is attached ?  Or each time you  
unplug then replug that storage ?  Are the names of your databases  
changing ?  Do you have some sort of automated system which works out  
which databases to open depending on which storage devices are  
attached ?

> and also the list will only return all the
> items if all the databases are present, a smaller subset otherwise. So
> i'd still have to check if the tables in the databases existed -  
> wouldn't i?

I think a solution would be to make another table which keeps track of  
which database is on which storage device, and keep that table in a  
consistent place which is always available.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Exists Query

[Andrew Gatt]

Simon Slavin wrote:
> On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:
>
>   
>> but by my scheme you can't be sure if the list_1 table exists in
>> database2,
>> 
>
> Add a column to your 'lists' tables which says which database they're  
> part of.  So every row of the database will have the same value.
>
>
>   
Unfortunately the database names change (they are assigned as and when
the storage is attached) and also the list will only return all the
items if all the databases are present, a smaller subset otherwise. So
i'd still have to check if the tables in the databases existed - wouldn't i?

Andrew

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Exists Query

[Andrew Gatt]

Roger Andersson wrote:
>
> I have multiple databases, both have a table called "lists" inside this
> table is the names of other tables in the database that make up the lists.
> Each individual list is made up of the tables that have the same name in
> each database (the list is split between different storage devices in this
> case).
> e.g.
>
> database 1
> "lists" list_1, list_2
> "list_1" item_1, item_2
> "list_2" item_1, item_2
>
> database 2
> "lists" list_1, list_3
> "list_1" item_3, item_4
> "list_3" item_1,item_2
>
> To get an output with all the lists of both databases i can use a union
> command on the "lists" table:
>
> select * from database1.lists union select * from database2.lists;
>
> However if i'm not sure which approach to take to get the contents of a
> specific list. I'm trying to replicate this sort of statement:
>
> select * from database1.list_2 union all select * from database2.list_2;
>
> but by my scheme you can't be sure if the list_1 table exists in database2,
> which if i'm right will error the statement and provide no output?
> The other option is to run a set of statements first checking whether the
> table exists and keeping a record of which database does and building the
> statement that way, but this seems inelegant. I was hoping someone would
> have seen this kind of behaviour before and could point me in the right
> direction of a solution? All comments are welcome.
>
> ___
> Maybe something like 
>
> select db1.* from database1.lists db1 union select db2.* from
> database2.lists db2;
> and
> select db1.* from database1.list_2 db1 union all select db2.* from
> database2.list_2 db2;
>
> /Roger
>   
Thanks for the suggestion, but for the second statement as list_2 does
not exist in database2 wouldn't this statement fail with a compile error
rather than give an output?

Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Exists Query

[Simon Slavin]

On 15 Aug 2009, at 3:12pm, Andrew Gatt wrote:

> but by my scheme you can't be sure if the list_1 table exists in
> database2,

Add a column to your 'lists' tables which says which database they're  
part of.  So every row of the database will have the same value.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table Exists Query

[Roger Andersson]

Maybe something like 

select db1.* from database1.lists db1 union select db2.* from
database2.lists db2;
and
select db1.* from database1.list_2 db1 union all select db2.* from
database2.list_2 db2;

/Roger
-Ursprungligt meddelande-
Från: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] För Andrew Gatt
Skickat: den 15 augusti 2009 16:13
Till: General Discussion of SQLite Database
Ämne: [sqlite] Table Exists Query

Hi all,

I have multiple databases, both have a table called "lists" inside this
table is the names of other tables in the database that make up the lists.
Each individual list is made up of the tables that have the same name in
each database (the list is split between different storage devices in this
case).
e.g.

database 1
"lists" list_1, list_2
"list_1" item_1, item_2
"list_2" item_1, item_2

database 2
"lists" list_1, list_3
"list_1" item_3, item_4
"list_3" item_1,item_2

To get an output with all the lists of both databases i can use a union
command on the "lists" table:

select * from database1.lists union select * from database2.lists;

However if i'm not sure which approach to take to get the contents of a
specific list. I'm trying to replicate this sort of statement:

select * from database1.list_2 union all select * from database2.list_2;

but by my scheme you can't be sure if the list_1 table exists in database2,
which if i'm right will error the statement and provide no output?
The other option is to run a set of statements first checking whether the
table exists and keeping a record of which database does and building the
statement that way, but this seems inelegant. I was hoping someone would
have seen this kind of behaviour before and could point me in the right
direction of a solution? All comments are welcome.

Thanks,
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Table Exists Query

[Andrew Gatt]

Hi all,

I have multiple databases, both have a table called "lists" inside this
table is the names of other tables in the database that make up the
lists. Each individual list is made up of the tables that have the same
name in each database (the list is split between different storage
devices in this case).
e.g.

database 1
"lists" list_1, list_2
"list_1" item_1, item_2
"list_2" item_1, item_2

database 2
"lists" list_1, list_3
"list_1" item_3, item_4
"list_3" item_1,item_2

To get an output with all the lists of both databases i can use a union
command on the "lists" table:

select * from database1.lists union select * from database2.lists;

However if i'm not sure which approach to take to get the contents of a
specific list. I'm trying to replicate this sort of statement:

select * from database1.list_2 union all select * from database2.list_2;

but by my scheme you can't be sure if the list_1 table exists in
database2, which if i'm right will error the statement and provide no
output?
The other option is to run a set of statements first checking whether
the table exists and keeping a record of which database does and
building the statement that way, but this seems inelegant. I was hoping
someone would have seen this kind of behaviour before and could point me
in the right direction of a solution? All comments are welcome.

Thanks,
Andrew
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 and negation operator

[cscs-sqlite]

Dan, updating to sqlite3.dll version 3.6.17 did fix the problem.

Also, I should clarify what I meant by having the query syntax doc on the
sqlite web site.  It is indeed on the web site, but I spent a lot of time
yesterday trying to find a document just like it and couldn't.  It just needs
to be in a more accessible spot, perhaps under the documents area?

Thanks again for the help,
Craig

> 
> Dan, thank you for the additional information, it's most helpful.  I'll let
> you know what I find out.
> 
> BTW, any reason that doc is not on the SQLite Web site?  It would help a
lot
> of people understand the old and new query syntax better.
> 
> Craig
> 
> > >
> > > Did an FTS3 update change how many negation operators (dash/-) can  
> > > be used in
> > > a
> > > match statement?
> > >
> > > For example, in sqlite3.dll version 3.5.7:
> > >
> > > colname match 'tetons -bend -jackson -oxbow* -parks' works as  
> > > expected; bend,
> > > jackson, oxbow* and parks are all removed from the results.
> > >
> > > but
> > >
> > > With, sqlite3.dll version 3.6.16:
> > >
> > > colname match 'tetons -bend -jackson -oxbow* -parks' no longer works
> > > correctly. It acts like only the last negated token is being used  
> > > and results
> > > have bend, jackson and oxbow* tokens in them.
> > 
> > Please try 3.6.17. I think this problem was fixed here:
> > 
> >http://www.sqlite.org/src/vdiff/27971
> > 
> > > Also, can somone point me to updated docs on how to use the new  
> > > options if
> > > SQLITE_ENABLE_FTS3_PARENTHESIS is set at compile time?
> > 
> >http://www.sqlite.org/src/annotate?mid=25265=373
> > 
> > Dan.
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3 and negation operator

[cscs-sqlite]

Dan, thank you for the additional information, it's most helpful.  I'll let
you know what I find out.

BTW, any reason that doc is not on the SQLite Web site?  It would help a lot
of people understand the old and new query syntax better.

Craig

> >
> > Did an FTS3 update change how many negation operators (dash/-) can  
> > be used in
> > a
> > match statement?
> >
> > For example, in sqlite3.dll version 3.5.7:
> >
> > colname match 'tetons -bend -jackson -oxbow* -parks' works as  
> > expected; bend,
> > jackson, oxbow* and parks are all removed from the results.
> >
> > but
> >
> > With, sqlite3.dll version 3.6.16:
> >
> > colname match 'tetons -bend -jackson -oxbow* -parks' no longer works
> > correctly. It acts like only the last negated token is being used  
> > and results
> > have bend, jackson and oxbow* tokens in them.
> 
> Please try 3.6.17. I think this problem was fixed here:
> 
>http://www.sqlite.org/src/vdiff/27971
> 
> > Also, can somone point me to updated docs on how to use the new  
> > options if
> > SQLITE_ENABLE_FTS3_PARENTHESIS is set at compile time?
> 
>http://www.sqlite.org/src/annotate?mid=25265=373
> 
> Dan.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] debugging sqlite with gdb

[Dan Kennedy]

On Aug 15, 2009, at 6:45 AM, udd...@cs.ucr.edu wrote:

> Hey,
> I am running sqlite-3.6.16. I am getting segmentation fault and so  
> trying
> to run sqlite with gdb. But I am getting following error-
>
> /sqlite-3.6.16/sqlite3": not in executable format: File format not  
> recognized

Could be an autoconf artifact. The real executable might be hidden away
in the .libs directory.

Dan.



> what to do?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] debugging sqlite with gdb

[uddinm]

Hey,
I am running sqlite-3.6.16. I am getting segmentation fault and so trying
to run sqlite with gdb. But I am getting following error-

/sqlite-3.6.16/sqlite3": not in executable format: File format not recognized

what to do?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [off topic] SQL pie chart

[Dennis Jenkins]

http://code.openark.org/blog/mysql/sql-pie-chart

 

An interesting project.  The author seems to have way more fun with SQL
than anyone should.  But my first thought was... I wonder if this can be
ported to sqlite?

 

 

Dennis Jenkins

Network Security Architect

iStream Financial Services

262-432-1560

 

CONFIDENTIALITY NOTICE

This electronic mail and the information contained herein are intended
for the named recipient only.  It may contain confidential, proprietary
and/or privileged information.  If you have received this electronic
mail in error, please do not read any text other than the text of this
notice and do not open any attachments. Also, please immediately notify
the sender by replying to this electronic mail or by collect call to
(262) 796-0925. After notifying the sender as described above, please
delete this electronic mail message immediately and purge the item from
the deleted items folder (or the equivalent) of your electronic mail
system. Thank you.

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Simon Slavin]

On 15 Aug 2009, at 9:58am, Sebastian Arcus wrote:

> I've just noticed another interesting thing. A read operation after a
> write on same table (like a SELECT statement after an INSERT) works
> perfectly fine - instantly. Which is bizarre, as I would have  
> expected a
> write to keep a much longer lock on the table then a read. So it's  
> only
> writes after reads that causes the lock.

Would it be possible to put the SQL commands into a text file, use the  
command-line utility to execute the text file, and time how long it  
takes ?

Since the command-line utility uses the bare sqlite function library  
and very little extra code, this would tell you whether the delay is  
being caused by the SQLite library on down, or some level above the  
SQLite library.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Roger Binns wrote:
>
> Sebastian Arcus wrote:
>   
>> Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
>> 1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
>> on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 
>> 
>
> That rules out disk activity as the cause.  Consequently I would pronounce
> your problem to be caused by whatever layers you have on top of SQLite.
> Another useful tool on Linux is ltrace which shows library calls.  Note
> however that it can't show calls made to libraries loaded via dlopen which
> is usually most of them.
>
>   
>> People seem to be talking about operations on tens to hundreds of 
>> thousands of records, and performance in some circumstances close to 
>> MySQL - it just seems all very far off from waiting 10 seconds on a 
>> single UPDATE or INSERT SQL statement. I must be doing something wrong.
>> 
>
> It will be the layer above SQLite causing the problem.  Disks are orders of
> magnitude slower than memory.  The performance choke point is having to do
> syncs to ensure data is on disk, but both SQLite and database servers have
> to do that.  SQLite then also benefits from running in the same process as
> whatever is using it, omitting network round trips.  In general the way
> servers win is by dedicating gigabytes of memory to caching but SQLite can
> also a lot if you increase various defaults.
>
>   
I've just noticed another interesting thing. A read operation after a 
write on same table (like a SELECT statement after an INSERT) works 
perfectly fine - instantly. Which is bizarre, as I would have expected a 
write to keep a much longer lock on the table then a read. So it's only 
writes after reads that causes the lock.

I just thought it was interesting to note.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite locking problem with mozStorage

[Sebastian Arcus]

Roger Binns wrote:
>
> Sebastian Arcus wrote:
>   
>> Hi and thanks for the suggestion. I did as you advised and ran 'vmstat 
>> 1' in a terminal. Very little activity - maybe 20-40kb every 6-7 seconds 
>> on the bo - pretty much nothing on bi. Also, zeros all around on so/si. 
>> 
>
> That rules out disk activity as the cause.  Consequently I would pronounce
> your problem to be caused by whatever layers you have on top of SQLite.
> Another useful tool on Linux is ltrace which shows library calls.  Note
> however that it can't show calls made to libraries loaded via dlopen which
> is usually most of them.
>
>   
>> People seem to be talking about operations on tens to hundreds of 
>> thousands of records, and performance in some circumstances close to 
>> MySQL - it just seems all very far off from waiting 10 seconds on a 
>> single UPDATE or INSERT SQL statement. I must be doing something wrong.
>> 
>
> It will be the layer above SQLite causing the problem.  Disks are orders of
> magnitude slower than memory.  The performance choke point is having to do
> syncs to ensure data is on disk, but both SQLite and database servers have
> to do that.  SQLite then also benefits from running in the same process as
> whatever is using it, omitting network round trips.  In general the way
> servers win is by dedicating gigabytes of memory to caching but SQLite can
> also a lot if you increase various defaults.
>
>   
Thanks Roger. I guess then I'm no further with this. I'm starting to 
wonder if the mozStorage library has such a small developer-user base 
(outside the actual Mozilla apps and Firefox/Thunderbird extensions) 
that I shouldn't expect to be able to get to the bottom of the thing. 
The strangest thing is that I remember few years back I've hit an almost 
identical road-block with a Microsoft Access database. After working 
fine for years - after some Windows update - it started taking 3-4 
seconds for one particular UPDATE operation. Never got to the bottom of 
that either :-) I had to include a progress bar on the form, and wait 4 
secs every time when attempting to save the record.

Thanks again for your suggestions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 15/08/2009 4:48 PM, Jim Showalter wrote:
> It doesn't collect those statistics automatically, as part of query 
> plan optimization?

You may like to consider looking at
"6.0 Choosing between multiple indices" in
http://www.sqlite.org/optoverview.html

HTH,

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

It doesn't collect those statistics automatically, as part of query 
plan optimization?

- Original Message - 
From: "Dan Kennedy" 
To: "General Discussion of SQLite Database" 
Sent: Friday, August 14, 2009 11:37 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:
>
>> How will that help him fix this problem, if the problem is that
>> SQLite's query optimizer is selecting a suboptimal index to use, 
>> and
>> there is no way to specify which index to use?
>
> The statistics collected by the ANALYZE command will be used by
> SQLite to (hopefully) select the optimal index.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[His Nerdship]

Thanks for the feedback. I realise that Dan's suggestions won't necessarily
fix the problem, but it would be very handy to know if my theory about poor
choice of indexes is right. So, Dan, take your bow!

- Original Message - 
From: "Dan Kennedy" 
To: "General Discussion of SQLite Database" 
Sent: Friday, August 14, 2009 11:15 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


> How will that help him fix this problem, if the problem is that 
> SQLite's query optimizer is selecting a suboptimal index to use, and 
> there is no way to specify which index to use?


>
> On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:
>
>>
>> Good day,
>> We have a puzzling problem with a large (1GB+) database.
>> Most of our queries are based on 3 columns, say X, Y and Z.
>> X is always the first in the index.  However, sometimes the query
>> involves a
>> small range of Y and a larger range of Z, and sometimes the
>> reverse.
-- 
View this message in context: 
http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981973.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:

> How will that help him fix this problem, if the problem is that
> SQLite's query optimizer is selecting a suboptimal index to use, and
> there is no way to specify which index to use?

The statistics collected by the ANALYZE command will be used by
SQLite to (hopefully) select the optimal index.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

How will that help him fix this problem, if the problem is that 
SQLite's query optimizer is selecting a suboptimal index to use, and 
there is no way to specify which index to use?

- Original Message - 
From: "Dan Kennedy" 
To: "General Discussion of SQLite Database" 
Sent: Friday, August 14, 2009 11:15 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:
>
>>
>> Good day,
>> We have a puzzling problem with a large (1GB+) database.
>> Most of our queries are based on 3 columns, say X, Y and Z.
>> X is always the first in the index.  However, sometimes the query
>> involves a
>> small range of Y and a larger range of Z, and sometimes the
>> reverse.  We
>> first had an index based on X, Y & Z (in that order), and noticed
>> that the
>> results are fast when there was one X, one Y and many Z's.  I check
>> if the
>> range is a single value, and if it is, I change the SQL to a
>> straight '=',
>> e.g:
>>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>>
>> According to Mike Owens, using an equality or IN operator on Y
>> allows Z to
>> be indexed, speeding up the search.  If Y is a range and we use
>> "BETWEEN y1
>> AND y2" on it, then Z will not be indexed.  This is what we found -
>> the
>> second search was much slower.
>>
>> However because sometimes the numbers are reversed, such that there
>> are many
>> Y's and few Z's, we added another index based on X, Z and Y, in 
>> that
>> order.
>> In this case, though, it didn't make any difference.  It seems like
>> SQLite
>> does not select the correct index to use - it uses XYZ instead of 
>> XZY.
>
> You might just need to run the ANALYZE command to collect database
> statistics.
>
>   http://www.sqlite.org/lang_analyze.html
>
> Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each
> query.
>
>   http://www.sqlite.org/lang_explain.html
>
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:

>
> Good day,
> We have a puzzling problem with a large (1GB+) database.
> Most of our queries are based on 3 columns, say X, Y and Z.
> X is always the first in the index.  However, sometimes the query  
> involves a
> small range of Y and a larger range of Z, and sometimes the  
> reverse.  We
> first had an index based on X, Y & Z (in that order), and noticed  
> that the
> results are fast when there was one X, one Y and many Z's.  I check  
> if the
> range is a single value, and if it is, I change the SQL to a  
> straight '=',
> e.g:
>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>
> According to Mike Owens, using an equality or IN operator on Y  
> allows Z to
> be indexed, speeding up the search.  If Y is a range and we use  
> "BETWEEN y1
> AND y2" on it, then Z will not be indexed.  This is what we found -  
> the
> second search was much slower.
>
> However because sometimes the numbers are reversed, such that there  
> are many
> Y's and few Z's, we added another index based on X, Z and Y, in that  
> order.
> In this case, though, it didn't make any difference.  It seems like  
> SQLite
> does not select the correct index to use - it uses XYZ instead of XZY.

You might just need to run the ANALYZE command to collect database  
statistics.

   http://www.sqlite.org/lang_analyze.html

Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each  
query.

   http://www.sqlite.org/lang_explain.html


Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple indexes in SQLite, and selecting which to use

[His Nerdship]

Good day,
We have a puzzling problem with a large (1GB+) database.
Most of our queries are based on 3 columns, say X, Y and Z.
X is always the first in the index.  However, sometimes the query involves a
small range of Y and a larger range of Z, and sometimes the reverse.  We
first had an index based on X, Y & Z (in that order), and noticed that the
results are fast when there was one X, one Y and many Z's.  I check if the
range is a single value, and if it is, I change the SQL to a straight '=',
e.g:
  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;

According to Mike Owens, using an equality or IN operator on Y allows Z to
be indexed, speeding up the search.  If Y is a range and we use "BETWEEN y1
AND y2" on it, then Z will not be indexed.  This is what we found - the
second search was much slower.

However because sometimes the numbers are reversed, such that there are many
Y's and few Z's, we added another index based on X, Z and Y, in that order. 
In this case, though, it didn't make any difference.  It seems like SQLite
does not select the correct index to use - it uses XYZ instead of XZY.
I know Mr Hipp is reluctant to add the ability to specify which index to use
- it is 'un-RDBMS like' in his words.
Is there any way the code can 'suggest' SQLite use a certain index?  Or at
least confirm which index is being used?

Also, is there a sensible maximum no of values we can put in an IN clause? 
Many of the queries involve all records over a month, and hitherto we have
used, say, ..WHERE Date BETWEEN 20090701 AND 20090731;
In this case would it work better with ..WHERE Date IN (20090701, 20090702,
20090703, ..., 20090731)?
Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users