Re: [sqlite] search

[Durga D]

Dear Michael.Black.

It's correct.

   I need to design database to store file paths and their info like
size. I have an idea
  item(file or folder), level0(imm. parent), level1(grand parent)
to level160(ancestor), type(file type or folder type).

 primary key: (item, level0 to level160)

 Is it correct approach? This is from server side. Need to store
millions of records.

Need optimum relationship between folders and files uniquely.

for ex: c:/mydocs/home/a.doc
c:/mydocs/office/agreement.doc

  insertion of filepaths,deltion of file paths are enough. should be able
to search by folder wise also.

   any ideas?

Thanks in advance.

On Tue, Dec 27, 2011 at 7:54 PM, Black, Michael (IS)  wrote:

> I don't know if FTS or a normal table will matter here but just normalize
> the whole thing.
>
>
>
> CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
>
> Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch
> faster.
>
>
>
> INSERT INTO virfts4 VALUES(1,'CO','country1');
>
> INSERT INTO virfts4 VALUES(1,'ST','state1');
>
> INSERT INTO virfts4 VALUES(1,'CI','city1');
>
> INSERT INTO virfts4 VALUES(1,'VI','village1');
>
> SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';
>
>
>
> You can store as many levels as you want.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Durga D [durga.d...@gmail.com]
> Sent: Tuesday, December 27, 2011 4:27 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] search
>
> Thank you. I agree. It's correct.
>
> I already have data base with /country/state/city/village format. Is it
> possible to do that while virtual table creation time?
>
> if yes, how?
>
> in case, if I have 250 levels like this ex: file system. how to do this.
> any idea?
>
> Thanks in advance.
>
> On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:
>
> > 2011/12/27 Durga D :
> > > select * from virfts4 where residence match '/*'; -- dint work
> > > how to get counties names from this db by using query?
> >
> > Normalize database to 1NF, e.g.
> > CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> > arrivtime, duration, imagelocation);
> > INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> > 0730, 1500,'C');
> >
> > then use select:
> > SELECT DISTINCT country FROM virfts4;
> > --
> > Kit
> > ___
> > 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-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] search

[Durga D]

Mohit,

  sqlite> .separator /
  sqlite> create virtual table virfts4 using fts4 (residence);
  sqlite> insert into  virfts4  select * from source
  sqlite> select count(*) from  virfts4  where residence match '/*'; --
result is 0.

  Please correct it. I think, i am doing something wrong.

Note: there is no spelling mistakes. all sql stmts compiled. but dint get
result. result should be greater than 0. it has 4 records.

Thanks in advance.

On Tue, Dec 27, 2011 at 7:40 PM, Mohit Sindhwani  wrote:

> On 27/12/2011 9:25 PM, Eduardo Morras wrote:
>
>> At 11:27 27/12/2011, you wrote:
>>
>>> Thank you. I agree. It's correct.
>>>
>>> I already have data base with /country/state/city/village format. Is it
>>> possible to do that while virtual table creation time?
>>>
>>> if yes, how?
>>>
>>
>> Change '/' to ','. This way you get a csv file which you can import
>> directly. Perhaps you need to add a line with the table column description.
>>
>>
> or at the SQLite3 shell do
> sqlite>separator /
> before doing a .import
>
> no?
>
> Cheers,
> Mohit.
> 27/12/2011 | 10:10 PM.
>
>
> __**_
> 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] (no subject)

[Pratibha Kulkarni]

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

Re: [sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

[陈正伟]

Or is there some way to use both 32-bit and 64-bit dll in one project?

I did some test, but they use same namespaces, I can't use them at same
time.

e.g. some code like this?
if (is_64_bit){
  db = new System.Data.Sqlite_64;
}else{
  db = new System.Data.Sqlite_32;
}




2011/12/28 Joe Mistachkin 

>
> >
> > is there a dll can be used on both system?
> >
>
> Not really.  Different native code is required for 32-bit and 64-bit
> processes.
>
> --
> Joe Mistachkin
>
> ___
> 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] 3.7.9 amalgamation file in VS2005

[Black, Michael (IS)]

I duplicated your problem on C++ 2005 Express and C++ 2008 Express.



C++ 2010 Express does the syntax highlighting correctly (or at least a lot 
better).



Can you upgrade?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexandr Němec [a.ne...@atlas.cz]
Sent: Tuesday, December 27, 2011 8:55 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] 3.7.9 amalgamation file in VS2005


Dear all,

I have one question that is not strictly a SQLite question (sorry), but maybe 
someone encountered this problem and found the solution. I have upgraded from 
an older SQLite release to 3.7.9 and loaded the amalgamation file into a Visual 
Studio 2005 project. But the syntax code highlighter does not behave correctly 
with the 3.7.9 amalgamation file because it greys out not only the sections of 
code that are not compiled at all (because of if(n)def's) but also other 
sections that DO compile. Well, I think that this is a bug of the VS 2005 
syntax highlighter for such a large source file, because the file compiles ok, 
but working with such a file in VS 2005 is frustrating.

Did anyone see (did anyone find a solution for) this problem? I have not seen 
this for older versions of the amalgamation file.

Best regards

Alex
___
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] EXT : Optimizing a query with range comparison.

[Black, Michael (IS)]

Does this type of insert need a begin/commit around it?  And would WAL mode 
make any difference?



And try increasing your cache_size to at least the size of your database file.



cache_size is in pages so the default of 2000 is 2MB.

#pragma cache_size=20

That'll make it 200MB which is probably more than enough for your example.



The query plan looks correct to my simple eye...

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE tableA (id INT, pos INT);
sqlite> CREATE TABLE tableB (start INT, end INT);
sqlite>
sqlite> CREATE INDEX tableA_idx on tableA (pos asc);
sqlite> CREATE INDEX tableB_idx on tableB (start asc, end asc);
sqlite>
sqlite> CREATE TABLE ids (id INT);
sqlite>
sqlite> explain query plan INSERT INTO ids SELECT id FROM tableA, tableB WHERE 
pos BETWEEN start AND end;
0|0|0|SCAN TABLE tableA (~100 rows)
0|1|1|SEARCH TABLE tableB USING COVERING INDEX tableB_idx (starthttp://www.sqlite.org/eqp.html. I will appreciate it if someone can
tell me what sqlite is doing for this query.

> explain select id from tableA, tableB where pos between start and end;
0|Trace|0|0|0||00|
1|Goto|0|26|0||00|
2|OpenRead|1|2|0|2|00|
3|OpenRead|0|1446|0|2|00|
4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00|
5|Rewind|1|22|0||00|
6|Column|1|0|1||00|
7|IsNull|1|21|0||00|
8|Affinity|1|1|0|d|00|
9|SeekGe|2|21|1|1|00|
10|Column|1|1|1||00|
11|IsNull|1|21|0||00|
12|Affinity|1|1|0|d|00|
13|IdxGE|2|21|1|1|01|
14|Column|2|0|2||00|
15|IsNull|2|20|0||00|
16|IdxRowid|2|2|0||00|
17|Seek|0|2|0||00|
18|Column|0|0|3||00|
19|ResultRow|3|1|0||00|
20|Next|2|13|0||00|
21|Next|1|6|0||01|
22|Close|1|0|0||00|
23|Close|0|0|0||00|
24|Close|2|0|0||00|
25|Halt|0|0|0||00|
26|Transaction|0|0|0||00|
27|VerifyCookie|0|6|0||00|
28|TableLock|0|2|0|tableB|00|
29|TableLock|0|1446|0|tableA|00|
30|Goto|0|2|0||00|

Many thanks in advance,
Bo
___
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] search

[Black, Michael (IS)]

I don't know if FTS or a normal table will matter here but just normalize the 
whole thing.



CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);

Your level can be CO, ST, CI, VI.  Or 1,2,3,4.  1,2,3,4 would be a touch faster.



INSERT INTO virfts4 VALUES(1,'CO','country1');

INSERT INTO virfts4 VALUES(1,'ST','state1');

INSERT INTO virfts4 VALUES(1,'CI','city1');

INSERT INTO virfts4 VALUES(1,'VI','village1');

SELECT DISTINCT value FROM virfts4 WHERE level MATCH 'CO';



You can store as many levels as you want.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Durga D [durga.d...@gmail.com]
Sent: Tuesday, December 27, 2011 4:27 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] search

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

[Joe Mistachkin]

>
> is there a dll can be used on both system?
>

Not really.  Different native code is required for 32-bit and 64-bit
processes.

--
Joe Mistachkin

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

Re: [sqlite] [shell] utf-8 bug

[Jens Frederich]

Thank you! What a mess...

On Tue, Dec 27, 2011 at 9:00 PM, Kevin Benson wrote:

> On 12/27/11, Jens Frederich  wrote:
> > The sqlite3 command line app doesn't write the string correctly to the
> > database file. It uses the terminal (cmd) encoding instead the 'PRAGMA
> > encoding' statement.
>
> chcp 65001 ...may not be a reliable avenue:
>
> https://www.google.com/search?&q=codepage+65001+Windows+WriteFile+bug
>
> --
> --
>   --
>  --
> --ô¿ô--
>K e V i N
> ___
> 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] [shell] utf-8 bug

[Kevin Benson]

On 12/27/11, Jens Frederich  wrote:
> The sqlite3 command line app doesn't write the string correctly to the
> database file. It uses the terminal (cmd) encoding instead the 'PRAGMA
> encoding' statement.

chcp 65001 ...may not be a reliable avenue:

https://www.google.com/search?&q=codepage+65001+Windows+WriteFile+bug

-- 
--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hi All, How to use System.data.sqlite on both 32-bit and 64-bit system.

[陈正伟]

Hi All,

I have a project with sqlite, I use System.data.sqlite.dll to connect the
sqlite database.

but some customer use 32-bit system, some use 64-bit system.

I know there is a 64-bit dll file, but I must build the project twice with
different dll.

is there a dll can be used on both system?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [shell] utf-8 bug

[Nico Williams]

On Tue, Dec 27, 2011 at 12:44 PM, Jens Frederich  wrote:
> The sqlite3 command line app doesn't write the string correctly to the
> database file. It uses the terminal (cmd) encoding instead the 'PRAGMA
> encoding' statement.

None of the SQLite3 code converts between encodings (other than UTF8<->UTF-16).

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

Re: [sqlite] [shell] utf-8 bug

[Jens Frederich]

I'm an UTF-8 expert. Code page 65001 is CP_UTF8, look here (
http://msdn.microsoft.com/en-us/library/windows/desktop/ms683169(v=vs.85).aspx
 ).

I know that code page 437 (alias OEM) works. But the ü-character isn't
correct encoded in the database file.
The UTF-8 hex byte sequence of ü is \xBC\xC3. If you look at the database
via hexdump or xxd you can see that the ü-character is encoded with the hex
sequence 0x81. That's the OEM encoding and not UTF-8. I use my database on
different operating systems. Thus, it is very important that all characters
are correctly UTF-8 encoded.

The sqlite3 command line app doesn't write the string correctly to the
database file. It uses the terminal (cmd) encoding instead the 'PRAGMA
encoding' statement.

Jens

On Tue, Dec 27, 2011 at 6:29 PM, Black, Michael (IS)  wrote:

> I"m not a UTF expert but codepage 437 seems to work fine for your example.
>
>
>
> codepage 65001 is not "real" UTF-8 according to several google sources.
>
>
>
> You do have to use Lucida font.
>
>
>
> C:\chcp 437
>
> Active code page: 437
>
> C:\sqlite test.db
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select length('füchsen');
> 7
> sqlite> .quit
>
> C:\chcp 65001
> Active code page: 65001
>
> C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual
> Studio 2005\Projects\sqlite\Debug>sqlite
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select length('füchsen');
> aborts
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Jens Frederich [jfreder...@gmail.com]
> Sent: Tuesday, December 27, 2011 1:59 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] [shell] utf-8 bug
>
> Hi all,
>
> I believe there is a bug in shell tool on Windows. I've tried to store
> utf-8 encoded literals in my test db. Do the following to reproduce the
> issue:
>
> 1. Open a Windows Console (cmd.exe)
> 2. chcp 65001  # change cmd.exe code page from 437(OEM) to utf-8
> 3. Run the shell
>   > sqlite3.exe test.db
>   > create table test(id integer primary key, value text);
>   > insert into test (id, value) values (1, 'füchsen'); # literial with
> multi byte characters
>
> The shell unexpectantly closed on the last command.
>
> What is the preferred way to save utf-8 encoded literals with the windows
> shell tool?
>
>  Jens
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing a query with range comparison.

[Bo Peng]

On Tue, Dec 27, 2011 at 10:47 AM, Igor Tandetnik  wrote:
> If you need to do this with any regularity, you should look at R-Tree
> module:
>
> http://www.sqlite.org/rtree.html

I do have a lot of range-based queries and rtree seems to be a perfect
solution for my problem. I am using the sqlite module from Python and
a good news is that, at least on Mac, RTREE is enabled by default
(python 2.7, Mac/BuildScript/build-installer.py has
-DSQLITE_ENABLE_RTREE). I will report back my if I can use this module
to optimize my query.

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

Re: [sqlite] Optimizing a query with range comparison.

[Simon Slavin]

On 27 Dec 2011, at 5:36pm, Igor Tandetnik wrote:

> On 12/27/2011 12:17 PM, Bo Peng wrote:
>> sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a
>> way to tell sqlite to return an id when it founds the first range that
>> the id falls into?
> 
> Without a temporary set to store the IDs it has already retrieved, how do you 
> expect SQLite to figure out whether a given range is in fact the first one a 
> given ID falls into?

Yeah.  You do need to store the results.  If you use

INSERT OR IGNORE

(or some equivalent way of keeping only the first found result) you can kind of 
hack it by relying on how SQLite uses indexes, but it is a hack and might fail 
in some future version of SQLite.

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

Re: [sqlite] Optimizing a query with range comparison.

[Igor Tandetnik]

On 12/27/2011 12:17 PM, Bo Peng wrote:

sqlite selects all ids before it uses B-TREE for DISTINCT. Is there a
way to tell sqlite to return an id when it founds the first range that
the id falls into?


Without a temporary set to store the IDs it has already retrieved, how 
do you expect SQLite to figure out whether a given range is in fact the 
first one a given ID falls into?

--
Igor Tandetnik

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

Re: [sqlite] [shell] utf-8 bug

[Black, Michael (IS)]

I"m not a UTF expert but codepage 437 seems to work fine for your example.



codepage 65001 is not "real" UTF-8 according to several google sources.



You do have to use Lucida font.



C:\chcp 437

Active code page: 437

C:\sqlite test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select length('füchsen');
7
sqlite> .quit

C:\chcp 65001
Active code page: 65001

C:\Documents and Settings\s360740.DIFL1ITE0017341\My Documents\Visual Studio 
2005\Projects\sqlite\Debug>sqlite
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select length('füchsen');
aborts





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jens Frederich [jfreder...@gmail.com]
Sent: Tuesday, December 27, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] [shell] utf-8 bug

Hi all,

I believe there is a bug in shell tool on Windows. I've tried to store
utf-8 encoded literals in my test db. Do the following to reproduce the
issue:

1. Open a Windows Console (cmd.exe)
2. chcp 65001  # change cmd.exe code page from 437(OEM) to utf-8
3. Run the shell
   > sqlite3.exe test.db
   > create table test(id integer primary key, value text);
   > insert into test (id, value) values (1, 'füchsen'); # literial with
multi byte characters

The shell unexpectantly closed on the last command.

What is the preferred way to save utf-8 encoded literals with the windows
shell tool?

  Jens
___
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] Optimizing a query with range comparison.

[Bo Peng]

>> Try using a JOIN instead.  In fact, try both ways around:
>>
>> DELETE FROM ids;
>> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN 
>> start AND end;
>>
>> then try
>>
>> DELETE FROM ids;
>> INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN 
>> start AND end;
>>
>> Which one is faster depends on some aspects about your data and it's easier 
>> for you to test it than for me to guess.
>
> If these two don't behave identically to each other and don't behave
> identically to the original query then there's bug in SQLite. With
> inner join it shouldn't matter for optimizer which form your query is
> written in.

Thank everyone for your quick replies. I trimmed down my tables to
have 10,000 and 1000 rows respectively, and re-create a test database
test1_fresh.DB WITHOUT analyze it.

bpeng@bp8:annoDB % cp test1_fresh.db test1.db
bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select
tableA.id from tableA join tableB on pos between start and end;'

real0m28.026s
user0m27.994s
sys 0m0.016s
bpeng@bp8:annoDB % sqlite3 test1.db 'select count(*) from ids;'
32486
bpeng@bp8:annoDB % cp test1_fresh.db test1.db
bpeng@bp8:annoDB % time sqlite3 test1.db 'insert into ids select
tableA.id from tableB join tableA on pos between start and end;'

real0m0.085s
user0m0.061s
sys 0m0.010s
bpeng@bp8:annoDB % sqlite3 test1.db 'select count(*) from ids;'
32486

To my surprise, the order of join has a significant impact on the
performance of query. The explain command shows why:

bpeng@bp8:annoDB % sqlite3 test1.db 'explain query plan insert into
ids select tableA.id from tableA join tableB on pos between start and
end;'
0|0|0|SCAN TABLE tableA (~100 rows)
0|1|1|SEARCH TABLE tableB USING COVERING INDEX tableB_idx (start? AND pos? AND pos? AND poshttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing a query with range comparison.

[Simon Slavin]

On 27 Dec 2011, at 4:46pm, Pavel Ivanov wrote:

>>> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND 
>>> end;
>> 
>> Try using a JOIN instead.  In fact, try both ways around:
>> 
>> DELETE FROM ids;
>> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN 
>> start AND end;
>> 
>> then try
>> 
>> DELETE FROM ids;
>> INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN 
>> start AND end;
>> 
>> Which one is faster depends on some aspects about your data and it's easier 
>> for you to test it than for me to guess.
> 
> If these two don't behave identically to each other and don't behave
> identically to the original query then there's bug in SQLite. With
> inner join it shouldn't matter for optimizer which form your query is
> written in.

After an 'ANALYZE', perhaps.  But without it my two forms give different 
'EXPLAIN QUERY PLAN' results to one-another, though one of them is the same as 
the OP's version.

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

Re: [sqlite] Optimizing a query with range comparison.

[Igor Tandetnik]

On 12/27/2011 10:39 AM, Bo Peng wrote:

The schema of my test tables is as follows:

CREATE TABLE tableA (id INT, pos INT);
CREATE TABLE tableB (start INT, end INT);

CREATE INDEX tableA_idx on tableA (pos asc);
CREATE INDEX tableB_idx on tableB (start asc, end asc);

CREATE TABLE ids (id INT);

tableA saves position of about 8 million objects, and table B saves
about 40 thousand ranges. I need to find out all ids in tableA that
falls into one of the ranges in tableB, and insert the results into
table ids.


If you need to do this with any regularity, you should look at R-Tree 
module:


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


I am using a query

INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end;

with indexes on tableA.pos and tableB.start, tableB.end (combined),
this query takes hours to execute.


An index on tableB(start, end) can only be used to satisfy one condition 
(pos >= start). After that, it's a linear scan.



Is there anyway to optimize this
query? My understanding is that, if the query takes each pos and
compare it to all ranges, it will be slow. If it takes each range and
get all pos fall into the range, the query will be much faster.


Try changing the order of tables in the FROM clause:

SELECT id FROM tableB, tableA WHERE pos BETWEEN start AND end;

All other things being equal (and here SQLite has no reason to believe 
they are not), SQLite tends to scan on the left hand side of the join, 
and search on the right hand side.


Alternatively, drop an index on TableB(start, end). It doesn't help much 
anyway, and without it, things are no longer equal and SQLite should 
choose the plan you want.


Note that, unless you know that ranges don't overlap, you may be getting 
duplicate IDs. You may want to change your query to


SELECT DISTINCT id FROM tableB, tableA WHERE pos BETWEEN start AND end;


I have
tried to 'EXPLAIN' the query but I do not understand the output


Use EXPLAIN QUERY PLAN instead. This produces a human-readable summary 
of the plan.

--
Igor Tandetnik

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

Re: [sqlite] Optimizing a query with range comparison.

[Pavel Ivanov]

>> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND 
>> end;
>
> Try using a JOIN instead.  In fact, try both ways around:
>
> DELETE FROM ids;
> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN start 
> AND end;
>
> then try
>
> DELETE FROM ids;
> INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start 
> AND end;
>
> Which one is faster depends on some aspects about your data and it's easier 
> for you to test it than for me to guess.

If these two don't behave identically to each other and don't behave
identically to the original query then there's bug in SQLite. With
inner join it shouldn't matter for optimizer which form your query is
written in.


For OP: please issue "EXPLAIN QUERY PLAN" instead of "EXPLAIN" on your
query. It will give more understandable information on how SQLite
processes your query.


Pavel


On Tue, Dec 27, 2011 at 11:39 AM, Simon Slavin  wrote:
>
> On 27 Dec 2011, at 3:39pm, Bo Peng wrote:
>
>> The schema of my test tables is as follows:
>>
>> CREATE TABLE tableA (id INT, pos INT);
>> CREATE TABLE tableB (start INT, end INT);
>>
>> CREATE INDEX tableA_idx on tableA (pos asc);
>> CREATE INDEX tableB_idx on tableB (start asc, end asc);
>>
>> CREATE TABLE ids (id INT);
>
> First, thanks a lot for posting that, which saves us all a huge amount of 
> guessing.
>
>> tableA saves position of about 8 million objects, and table B saves
>> about 40 thousand ranges. I need to find out all ids in tableA that
>> falls into one of the ranges in tableB, and insert the results into
>> table ids. I am using a query
>
> So you don't care how many ranges in tableB an object falls into, you just 
> want it to appear once ?  Or your data is structures so that ranges don't 
> overlap ?  Either way, what you really want for ids is something more like
>
> CREATE TABLE ids (id INTEGER PRIMARY KEY, ON CONFLICT IGNORE);
>
> To understand this better, read
>
> 
>
>> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND 
>> end;
>
> Try using a JOIN instead.  In fact, try both ways around:
>
> DELETE FROM ids;
> INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN start 
> AND end;
>
> then try
>
> DELETE FROM ids;
> INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start 
> AND end;
>
> Which one is faster depends on some aspects about your data and it's easier 
> for you to test it than for me to guess.
>
> Simon.
> ___
> 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] Optimizing a query with range comparison.

[Simon Slavin]

On 27 Dec 2011, at 3:39pm, Bo Peng wrote:

> The schema of my test tables is as follows:
> 
> CREATE TABLE tableA (id INT, pos INT);
> CREATE TABLE tableB (start INT, end INT);
> 
> CREATE INDEX tableA_idx on tableA (pos asc);
> CREATE INDEX tableB_idx on tableB (start asc, end asc);
> 
> CREATE TABLE ids (id INT);

First, thanks a lot for posting that, which saves us all a huge amount of 
guessing.

> tableA saves position of about 8 million objects, and table B saves
> about 40 thousand ranges. I need to find out all ids in tableA that
> falls into one of the ranges in tableB, and insert the results into
> table ids. I am using a query

So you don't care how many ranges in tableB an object falls into, you just want 
it to appear once ?  Or your data is structures so that ranges don't overlap ?  
Either way, what you really want for ids is something more like

CREATE TABLE ids (id INTEGER PRIMARY KEY, ON CONFLICT IGNORE);

To understand this better, read



> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end;

Try using a JOIN instead.  In fact, try both ways around:

DELETE FROM ids;
INSERT INTO ids SELECT tableA.id FROM tableA JOIN tableB ON pos BETWEEN start 
AND end;

then try

DELETE FROM ids;
INSERT INTO ids SELECT tableA.id FROM tableB JOIN tableA ON pos BETWEEN start 
AND end;

Which one is faster depends on some aspects about your data and it's easier for 
you to test it than for me to guess.

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

Re: [sqlite] 3.7.9 amalgamation file in VS2005

[Teg]

VS2010  has  the  same problem. The file is simply too big for it. I'm
not  a  fan  of  the amalgamation  because  of  this.  It's essentially
impossible to trace into the SQlite code now.

That said, it compiles and works fine.

C

Tuesday, December 27, 2011, 10:50:14 AM, you wrote:

JJD> In my experience I find that most syntax highlighters fail
JJD> on occasion, especially with large files.  Trust the compiler not the
JJD> editor.

JJD> 2011/12/27 Alexandr Němec 

>>
>> Dear all,
>>
>> I have one question that is not strictly a SQLite question (sorry), but
>> maybe someone encountered this problem and found the solution. I have
>> upgraded from an older SQLite release to 3.7.9 and loaded the amalgamation
>> file into a Visual Studio 2005 project. But the syntax code highlighter
>> does not behave correctly with the 3.7.9 amalgamation file because it greys
>> out not only the sections of code that are not compiled at all (because of
>> if(n)def's) but also other sections that DO compile. Well, I think that
>> this is a bug of the VS 2005 syntax highlighter for such a large source
>> file, because the file compiles ok, but working with such a file in VS 2005
>> is frustrating.
>>
>> Did anyone see (did anyone find a solution for) this problem? I have not
>> seen this for older versions of the amalgamation file.
>>
>> Best regards
>>
>> Alex
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>






-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Optimizing a query with range comparison.

[Don V Nielsen]

I do something similar, where the ranges are zip codes.  However, my tableb
is arranged vertically with one key (zip code) and one value (geographic
zone).  I would then join the two tables using the zip code, rather than
trying to identify the zip code within a range of zip codes in tableb.
Matching 3 million tablea rows to 49,000 tableb rows takes 90 seconds, I
think.


create tablea (id int, pos int);
create tableb (pos int, ?? int);  /* not sure what is represented by index
position of range */

create table ids as
select ?? from tableb b join tablea a on b.pos = a.pos;


On Tue, Dec 27, 2011 at 9:39 AM, Bo Peng  wrote:

> Dear Sqlite experts,
>
> The schema of my test tables is as follows:
>
> CREATE TABLE tableA (id INT, pos INT);
> CREATE TABLE tableB (start INT, end INT);
>
> CREATE INDEX tableA_idx on tableA (pos asc);
> CREATE INDEX tableB_idx on tableB (start asc, end asc);
>
> CREATE TABLE ids (id INT);
>
> tableA saves position of about 8 million objects, and table B saves
> about 40 thousand ranges. I need to find out all ids in tableA that
> falls into one of the ranges in tableB, and insert the results into
> table ids. I am using a query
>
> INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND
> end;
>
> with indexes on tableA.pos and tableB.start, tableB.end (combined),
> this query takes hours to execute. Is there anyway to optimize this
> query? My understanding is that, if the query takes each pos and
> compare it to all ranges, it will be slow. If it takes each range and
> get all pos fall into the range, the query will be much faster. I have
> tried to 'EXPLAIN' the query but I do not understand the output
> because it looks different from what is described in
> http://www.sqlite.org/eqp.html. I will appreciate it if someone can
> tell me what sqlite is doing for this query.
>
> > explain select id from tableA, tableB where pos between start and end;
> 0|Trace|0|0|0||00|
> 1|Goto|0|26|0||00|
> 2|OpenRead|1|2|0|2|00|
> 3|OpenRead|0|1446|0|2|00|
> 4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00|
> 5|Rewind|1|22|0||00|
> 6|Column|1|0|1||00|
> 7|IsNull|1|21|0||00|
> 8|Affinity|1|1|0|d|00|
> 9|SeekGe|2|21|1|1|00|
> 10|Column|1|1|1||00|
> 11|IsNull|1|21|0||00|
> 12|Affinity|1|1|0|d|00|
> 13|IdxGE|2|21|1|1|01|
> 14|Column|2|0|2||00|
> 15|IsNull|2|20|0||00|
> 16|IdxRowid|2|2|0||00|
> 17|Seek|0|2|0||00|
> 18|Column|0|0|3||00|
> 19|ResultRow|3|1|0||00|
> 20|Next|2|13|0||00|
> 21|Next|1|6|0||01|
> 22|Close|1|0|0||00|
> 23|Close|0|0|0||00|
> 24|Close|2|0|0||00|
> 25|Halt|0|0|0||00|
> 26|Transaction|0|0|0||00|
> 27|VerifyCookie|0|6|0||00|
> 28|TableLock|0|2|0|tableB|00|
> 29|TableLock|0|1446|0|tableA|00|
> 30|Goto|0|2|0||00|
>
> Many thanks in advance,
> Bo
> ___
> 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] 3.7.9 amalgamation file in VS2005

[Jim "Jed" Dodgen]

In my experience I find that most syntax highlighters fail
on occasion, especially with large files.  Trust the compiler not the
editor.

2011/12/27 Alexandr Němec 

>
> Dear all,
>
> I have one question that is not strictly a SQLite question (sorry), but
> maybe someone encountered this problem and found the solution. I have
> upgraded from an older SQLite release to 3.7.9 and loaded the amalgamation
> file into a Visual Studio 2005 project. But the syntax code highlighter
> does not behave correctly with the 3.7.9 amalgamation file because it greys
> out not only the sections of code that are not compiled at all (because of
> if(n)def's) but also other sections that DO compile. Well, I think that
> this is a bug of the VS 2005 syntax highlighter for such a large source
> file, because the file compiles ok, but working with such a file in VS 2005
> is frustrating.
>
> Did anyone see (did anyone find a solution for) this problem? I have not
> seen this for older versions of the amalgamation file.
>
> Best regards
>
> Alex
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

*Jim Dodgen*


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

[sqlite] Optimizing a query with range comparison.

[Bo Peng]

Dear Sqlite experts,

The schema of my test tables is as follows:

CREATE TABLE tableA (id INT, pos INT);
CREATE TABLE tableB (start INT, end INT);

CREATE INDEX tableA_idx on tableA (pos asc);
CREATE INDEX tableB_idx on tableB (start asc, end asc);

CREATE TABLE ids (id INT);

tableA saves position of about 8 million objects, and table B saves
about 40 thousand ranges. I need to find out all ids in tableA that
falls into one of the ranges in tableB, and insert the results into
table ids. I am using a query

INSERT INTO ids SELECT id FROM tableA, tableB WHERE pos BETWEEN start AND end;

with indexes on tableA.pos and tableB.start, tableB.end (combined),
this query takes hours to execute. Is there anyway to optimize this
query? My understanding is that, if the query takes each pos and
compare it to all ranges, it will be slow. If it takes each range and
get all pos fall into the range, the query will be much faster. I have
tried to 'EXPLAIN' the query but I do not understand the output
because it looks different from what is described in
http://www.sqlite.org/eqp.html. I will appreciate it if someone can
tell me what sqlite is doing for this query.

> explain select id from tableA, tableB where pos between start and end;
0|Trace|0|0|0||00|
1|Goto|0|26|0||00|
2|OpenRead|1|2|0|2|00|
3|OpenRead|0|1446|0|2|00|
4|OpenRead|2|133259|0|keyinfo(1,BINARY)|00|
5|Rewind|1|22|0||00|
6|Column|1|0|1||00|
7|IsNull|1|21|0||00|
8|Affinity|1|1|0|d|00|
9|SeekGe|2|21|1|1|00|
10|Column|1|1|1||00|
11|IsNull|1|21|0||00|
12|Affinity|1|1|0|d|00|
13|IdxGE|2|21|1|1|01|
14|Column|2|0|2||00|
15|IsNull|2|20|0||00|
16|IdxRowid|2|2|0||00|
17|Seek|0|2|0||00|
18|Column|0|0|3||00|
19|ResultRow|3|1|0||00|
20|Next|2|13|0||00|
21|Next|1|6|0||01|
22|Close|1|0|0||00|
23|Close|0|0|0||00|
24|Close|2|0|0||00|
25|Halt|0|0|0||00|
26|Transaction|0|0|0||00|
27|VerifyCookie|0|6|0||00|
28|TableLock|0|2|0|tableB|00|
29|TableLock|0|1446|0|tableA|00|
30|Goto|0|2|0||00|

Many thanks in advance,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 3.7.9 amalgamation file in VS2005

[Alexandr Němec]

Dear all,
 
I have one question that is not strictly a SQLite question (sorry), but maybe 
someone encountered this problem and found the solution. I have upgraded from 
an older SQLite release to 3.7.9 and loaded the amalgamation file into a Visual 
Studio 2005 project. But the syntax code highlighter does not behave correctly 
with the 3.7.9 amalgamation file because it greys out not only the sections of 
code that are not compiled at all (because of if(n)def's) but also other 
sections that DO compile. Well, I think that this is a bug of the VS 2005 
syntax highlighter for such a large source file, because the file compiles ok, 
but working with such a file in VS 2005 is frustrating.
 
Did anyone see (did anyone find a solution for) this problem? I have not seen 
this for older versions of the amalgamation file.
 
Best regards
 
Alex
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Eduardo Morras]

At 15:10 27/12/2011, Mohit Sindhwani wrote:

On 27/12/2011 9:25 PM, Eduardo Morras wrote:

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column description.


or at the SQLite3 shell do
sqlite>separator /
before doing a .import

no?


Yes, didn't remember.


Cheers,
Mohit.



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

Re: [sqlite] search

[Mohit Sindhwani]

On 27/12/2011 9:25 PM, Eduardo Morras wrote:

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column 
description.




or at the SQLite3 shell do
sqlite>separator /
before doing a .import

no?

Cheers,
Mohit.
27/12/2011 | 10:10 PM.

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

Re: [sqlite] search

[Eduardo Morras]

At 11:27 27/12/2011, you wrote:

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?


Change '/' to ','. This way you get a csv file which you can import 
directly. Perhaps you need to add a line with the table column description.




in case, if I have 250 levels like this ex: file system. how to do this.
any idea?


You can use a shell script to do so. I leave it as exercise to you ;)


Thanks in advance.


HTH 



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

Re: [sqlite] search

[Durga D]

Thank you. I agree. It's correct.

I already have data base with /country/state/city/village format. Is it
possible to do that while virtual table creation time?

if yes, how?

in case, if I have 250 levels like this ex: file system. how to do this.
any idea?

Thanks in advance.

On Tue, Dec 27, 2011 at 3:38 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > select * from virfts4 where residence match '/*'; -- dint work
> > how to get counties names from this db by using query?
>
> Normalize database to 1NF, e.g.
> CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
> arrivtime, duration, imagelocation);
> INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
> 0730, 1500,'C');
>
> then use select:
> SELECT DISTINCT country FROM virfts4;
> --
> Kit
> ___
> 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] search

[Kit]

2011/12/27 Durga D :
> select * from virfts4 where residence match '/*'; -- dint work
> how to get counties names from this db by using query?

Normalize database to 1NF, e.g.
CREATE VIRTUAL TABLE virfts4 using fts4(country, state, city, village,
arrivtime, duration, imagelocation);
INSERT INTO virfts4 VALUES ('country1','state1','city1','village1',
0730, 1500,'C');

then use select:
SELECT DISTINCT country FROM virfts4;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search

[Durga D]

Hi kit,

   Thanks for your response.

CREATE VIRTUAL TABLE virfts4 using fts4(residence, arrivtime, duration,
imagelocation);

insert into virfts4 values('/country1/state1/city1/village1', 0730, 1500,
'C');
insert into virfts4 values('/country1/state1/city1/village2', 0731, 1500,
'C');
insert into virfts4 values('/countr1/state1/village1/', 1800, 1000, 'D');

select * from virfts4 where residence match '/*'; -- dint work
how to get counties names from this db by using query?

select * from virfts4 where residence match '/c*'; -- it's worked and very
fast.

Thanks in advance.

On Tue, Dec 27, 2011 at 2:01 PM, Kit  wrote:

> 2011/12/27 Durga D :
> > HI all,
> >   I have sqlite database with more than 1000 records. Here,
> > residencearea is the primary key.
> > /country/state/city/village
> >   I tried with fts3 and fts4 virtual tables. Not much performance.
> >   like query on direct table, dead slow.
>
> Send your SQL query and table structure.
>
> Maybe you used LIKE instead of MATCH.
> --
> Kit
> ___
> 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] search

[Kit]

2011/12/27 Durga D :
> HI all,
>   I have sqlite database with more than 1000 records. Here,
> residencearea is the primary key.
> /country/state/city/village
>   I tried with fts3 and fts4 virtual tables. Not much performance.
>   like query on direct table, dead slow.

Send your SQL query and table structure.

Maybe you used LIKE instead of MATCH.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users