[sqlite] LSM INT key problem
Both SQlite.exe and lsm.dll are compiled on Win10 with Mingwin64. Lsm by copying sqlite3.h and sqlite3ext.h to the lsm1 directory and then: make lsm.so TCCX="gcc -g -O2" and rename to lsm.dll Creating a lsm table with an INT key results in the following: SQLite version 3.28.0 2019-04-16 19:49:53 [...] sqlite> .load lsm sqlite> CREATE VIRTUAL TABLE test USING lsm1 ( ...> 'test.lsm', idx, INT, d ...> ); Error: key type should be INT, TEXT, or BLOB sqlite> CREATE VIRTUAL TABLE test USING lsm1 ( ...> 'test.lsm', idx, INTEGER, d ...> ); Error: key type should be INT, TEXT, or BLOB Using TXT or BLOB there is no error. Did I go wrong somewhere? Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 'where ... isnull' in create index
For the second insert in the code below I expected a failure. From the diagrams in the create index doc I understand the WHERE ts_to ISNULL is legal. Do I misunderstand the docs or is there an other place where I should look. I'm awar that I could use some future data as default for ts_to but it is not elegant to me, TIA, ingo ---%<--%<--%<--- CREATE TABLE IF NOT EXISTS person ( pid INTEGER NOT NULL, full_name TEXT, ts_to TEXT DEFAULT NULL ); CREATE UNIQUE INDEX IF NOT EXISTS idx_person ON person (pid, ts_to) WHERE ts_to ISNULL ; INSERT INTO person (pid, full_name) VALUES (1,'pietje puk'); INSERT INTO person (pid, full_name) VALUES (1,'jan tabak'); -> should fail?? SELECT * FROM person WHERE ts_to ISNULL; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] 'where ... isnull' in create index
On 5-6-2019 09:38, Hick Gunter wrote: > NULL is considered different from any other value, including another NULL, in > the context of UNIQUE. > > So your unique index has 2 entries (1, NULL1) for rowid 1 and (1, NULL2) for > rowid2. > > See https://sqlite.org/nulls.html > > It also states that NULL1 == NULL2 is TRUE in UNION and DISTINCT contices. > Thanks, ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] upsert unique partial index
First it tells me an unique constraint failed and then it can't find it? CREATE TABLE testupsert ( id INTEGER NOT NULL, param TEXT NOT NULL DEFAULT '_', sometxt TEXT ); CREATE UNIQUE INDEX up ON testupsert (id, param) WHERE param = '_'; INSERT INTO testupsert (id, sometxt) VALUES (1,'1'), (2,'2'); INSERT INTO testupsert (id, sometxt) VALUES (1,'test') --as expected --Error: UNIQUE constraint failed: testupsert.id, testupsert.param INSERT INTO testupsert (id, sometxt) VALUES (1,'test') ON CONFLICT (id, param) DO UPDATE SET param = 'updated'; --Error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] upsert unique partial index
On 5-6-2019 12:52, Richard Hipp wrote: > WHERE param='_' query executed, thanks, ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Upsert inside trigger?
Upon creation, the trigger below gives a syntax error near INSERT. Without the trigger surrounding it, the query works well. The docs give me no clue to what goes wrong. Ingo ---%<--%<--%<--- CREATE TRIGGER IF NOT EXISTS update_balances AFTER INSERT ON journal BEGIN WITH inup(account_id, value_balance, amount_balance) AS ( --ledgers is a view SELECT ledgers.account_id, SUM(ledgers.asset_value), SUM(ledgers.asset_amount) FROM ledgers WHERE ledgers.account_id = 11 ) INSERT INTO balances(account_id, value_balance, amount_balance) VALUES ( (SELECT account_id FROM inup), (SELECT value_balance FROM inup), (SELECT amount_balance FROM inup) ) ON CONFLICT (balances.account_id) DO UPDATE SET value_balance = (SELECT value_balance FROM inup), amount_balance= (SELECT amount_balance FROM inup) WHERE account_id = 11 ; END; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Upsert inside trigger?
On 16-6-2019 15:11, Adrian Ho wrote: > Common table expression are not supported for statements inside of > triggers. Ah, I searched the docs for 'upsert', 'with' ... Thanks. ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested set tree: how to change order of one node?
Sam, Can't answer your question directly, maybe the closure extension is something for you. To read a bit about it: http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ ingo On 18-6-2019 14:19, Sam Carleton wrote: > My thought process is to do this: > >1. create a temp table to hold all the descendants of the parent >2. copy the subordinates (and descendants) into the temp table one at a >time in the new order to get the lft/rgt values correct >3. Once all the children and descendants are copied into the temp table, >update the lft/rgt values of the source table to get the new order > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] misuse of aggregate function max()
CREATE TABLE test( id INTEGER NOT NULL, ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, ts_eol TEXT DEFAULT NULL ); CREATE UNIQUE INDEX idx_test ON test(id, max(ts_from), ts_eol) WHERE ts_eol = NULL ; --Error: misuse of aggregate function max() Is this because max() is not deterministic, or because current_timestamp is not, or both? Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] misuse of aggregate function max()
On 21-6-2019 11:46, Simon Slavin wrote: > You will note that SQLite is perfectly happy with > > CREATE UNIQUE INDEX idx_test_c >ON test(id, ts_from, ts_eol) > WHERE ts_eol = NULL > ; That is what I use now, together with the select as Hick mentioned. An other version I played with is two indexes, one unique on (id, ts_from) to find the last version if eol is not null and one unique on (id, ts_eol) where eol = null to find the current active version of id. Thanks, Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...
On 26-6-2019 22:22, Warren Young wrote: > 3. Lack of types. Not being a programmer, that was a revelation to me, I started with Postgresql (upgrading to SQLite now) and wasted way to many hours on deciding the type. In SQLite it is straight forward. If I use CURRENT_TIMESTAMP in the shell it shows me some text so that's the type I choose. If I need finer granularity I do it in the application, not in the library unless there is a specific function for it. Regarding the docs, they are dense and not always clear to me as a non native speaker. Slowly I'm seeing a pattern though, start with the diagrams and if the SQL does not work, find the exception why in the text. Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] wal
From the docs, "The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database." When using 'single shot' access to the database, with no other connections, I see a wal file being created and deleted. Just for my understanding, would it be of advantage to have a second persistent connection just for keeping the wal alive? (I have no real world scenario for this, I just saw this happen and wondered while setting up SQLTools on Sublime for SQLite. It doesn't/can't create a persistent connection) Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] wal
Haha :) Thanks for the insight. Hadn't looked at it that way. Ingo On 28-6-2019 11:52, Warren Young wrote: > You’ve basically got it backwards. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] json_group_array( json_object())
The following:
json_object (
'data', json_group_array(
json_object(
'type', type,
'id', notebook_id,
'attributes', json_object(
'book', book,
'total_notes', total_notes
), ...etc
results in:
{"data":"[{\"type\":\"notebook\",\"id\":2,\"attributes\":{\"book\":\"brew\",\...
etc
the array is a string?
besides manually constructing with:
|| json_quotes('data')
|| ":["
|| group_concat(
json_object( )
)
how should it be done properly in one go?
TIA,
ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] json_group_array( json_object())
On 5-7-2019 20:14, Richard Hipp wrote: > Can you please send a complete example? While preparing that the problem was resolved. A search showed that a wrong concatenation || further down caused this result. Something I noticed before when working with json is that my errors in the code result in strange results and not in an error. Thanks Richard, Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to create a new table or overwrite an existing table in one command?
On 4-9-2019 12:24, Rob Willett wrote: > Peng, > > Dropping very large tables is time consuming. Dropping a 59GB table > takes quite a long time for us even on fast hardware. Dropping smaller > tables is faster though. > When using (and dropping) this big tables, would it be of advantage to put only that one table in a separate database and attach it when needed. There would be no need then to drop it, one could just detach and delete the db. Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
On 27-1-2020 23:18, Richard Hipp wrote: > For many years I have described SQLite as being "serverless", as a way > to distinguish it from the more traditional client/server design of > RDBMSes. "Serverless" seemed like the natural term to use, as it > seems to mean "without a server". > client-serverless? although I've always thought of it as an in-proces DB-library. ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New SQLite Forum established - this mailing list is deprecated
Can we clone (push, pull, sync) the forum fossil? Ingo On 12-3-2020 21:17, Richard Hipp wrote: The Forum is powered by Fossil. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re: How to sort not binary?
Yves Goergen wrote: > I guess that doesn't work when I'm accessing the database through the > System.Data.SQLite interface in .NET? Fortunately your guess is wrong. ;-) System.Data.SQLite supports user defined collation sequences. See TestCases.cs of the source distribution for samples how to implement them. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to sort not binary?
Yves Goergen schrieb: > On 13.05.2007 17:19 CE(S)T, Chris Wedgwood wrote: >> On Sun, May 13, 2007 at 05:07:16PM +0200, Yves Goergen wrote: >> >>> Ah, now I realised that I'd also like to have that "natural sorting", >>> meaning this: >>> >>> 2 >>> 8 >>> 9 >>> 10 >>> 11 >>> 23 >> select from from table order by cast( as text); > > I'm not sure what you wanted to say with this. I removed the first > "from" to make it work but it does the same as without the cast. It > still sorts strings beginning with "10" before those beginning with "2". Yep, because the statement is wrong for your case. I guess he misunderstood you somehow. select from order by cast ( as integer) is what you wanted. But: If your column is a text column holding strings and numbers or strings beginning with numbers the statement above won't work as expected. All rows starting with alpha text will be placed at the beginning of the result. They all return 0 as the integer value and are not sorted but returned in the order they have been added to the table. If your column to sort on only holds integer values and you defined the column as INTEGER your "natural sorting" should be handled by SQLite. If you have strings *and* numbers in your columns then write a user defined collation sequence which handles this case. It's up to you. ;-) - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Subselect question
Hi, I've got a question concerning a query with subselects. I have a table with stores pairs of events. one of the events is kind of a start event and the other one is a stop event. Each event is stored in its own row. What I'm trying to achive is to get a view which contains rows with the start event and the corresponding stop event in one row. It works somehow, but only somehow. :-( Here is some test data: - CREATE TABLE "TBOOKING" ( "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "EVENTTIMESTAMP" TIMESTAMP NOT NULL, "EVENTTYPE" INTEGER NOT NULL, "EMPLOYEE" INTEGER); INSERT INTO "TBOOKING" VALUES(42,'2008-09-22 09:19:35.000',3,NULL); INSERT INTO "TBOOKING" VALUES(43,'2008-09-22 09:24:50.000',4,NULL); INSERT INTO "TBOOKING" VALUES(44,'2008-09-22 10:43:03.000',3,NULL); INSERT INTO "TBOOKING" VALUES(45,'2008-09-22 10:48:46.000',4,NULL); INSERT INTO "TBOOKING" VALUES(46,'2008-09-22 11:56:56.000',3,NULL); INSERT INTO "TBOOKING" VALUES(47,'2008-09-22 12:01:13.000',4,NULL); INSERT INTO "TBOOKING" VALUES(48,'2008-09-22 14:23:05.000',3,NULL); INSERT INTO "TBOOKING" VALUES(49,'2008-09-22 14:27:11.000',4,NULL); - Here is the select for the view: - SELECT A.ID AS ID1, A.EVENTTIMESTAMP AS TS1, A.EVENTTYPE AS ET1, (SELECT B.ID FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.ID>A.ID LIMIT 1) AS ID2, (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.ID>A.ID LIMIT 1) AS TS2, (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.ID>A.ID LIMIT 1) AS ET2 FROM TBOOKING AS A WHERE A.EVENTTYPE=3; - and here is the result: - RecNo ID1 TS1 ET1 ID2 TS2 ET2 - --- --- --- --- --- --- 1 43 22.09.2008 09:19:35 3 43 22.09.2008 09:24:50 4 2 45 22.09.2008 10:43:03 3 45 22.09.2008 10:48:46 4 3 47 22.09.2008 11:56:56 3 47 22.09.2008 12:01:13 4 4 49 22.09.2008 14:23:05 3 49 22.09.2008 14:27:11 4 - Have a look at the column ID1. It should contain the values 42,44,46, and 48. Is this a bug, or am I doing something wrong? Thanks for your answers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
Csaba wrote: > Firstly, perhaps you should be linking the start and stop event across > a common id rather than relying on a start and stop appearing as > consecutive entries. Without knowing more about where your database > comes from it's hard to say. > > If you insist on keeping the current structure, here's a way to get > what you want (you'll have to set the columns you want to keep as > appropriate): > > SELECT t.*, u.* > FROM TBOOKING AS t LEFT JOIN TBOOKING as u > ON t.ID+1=u.ID > WHERE t.EVENTTYPE+1=u.EVENTTYPE The problem with the join is, that although t.ID+1=u.ID is most often the case, it's not guaranteed that t.ID+1=u.ID. Users may undo the last booking (by deleting it from the database) which is why the subselects have u.ID>t.ID as part of the where clause. So the join, no matter how simple and elegant it would be, isn't a choice. > If, however, you to have a common Id, as mentioned above, for paired > event start and stop rows, call it EventId, then you could do: > SELECT t.*, u.* > FROM TBOOKING AS t LEFT JOIN TBOOKING as u > ON t.EventId=u.EventId > WHERE t.EVENTTYPE+1=u.EVENTTYPE I thought about that too, but I didn't want to keep track of an additional ID in the application but instead let the database do the work. Maybe an additional table for the current eventid and a on insert trigger could do the trick without changing the application logic. I'll think about it. Thanks for your answer. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
Griggs, Donald wrote: > When you wrote: "... but I didn't want to keep track of an additional > ID in the application but instead let the database do the work." > > I don't think I understand what logic the database is supposed to use to > determine this. If you were talking to a database that was as smart as > a human, how would you instruct it to choose the proper record without > an eventId? Well, I don't need an eventId in the application. All I need is the information about the startevent and the corresponding stopevent. SQLite *can* collect the necessary information (see the select in my initial posting) for *my usecase*. The misbehaviour that I reported is confirmed as a bug and corrected by Dan. So the database hasn't to be as smart as a human, it only has to be as smart as SQLite, and I try to instruct it with my limited SQL knowledge. ;-) I, personally, try to reduce application logic to simple insert, select and delete statements. Anything else related to the data stored in the database and the relations between the tables should be handled by the database itself (with the help of the application programmer of course by means of triggers, database procedures and functions). Normally, if handled internally, the database is much faster to do the adequate things than an application through an interface can do. And as a result of that this is the single point on my wish list for SQLite: Stored procedures and functions with support for variables. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
Igor Tandetnik wrote: > How about this: > > select A.*, B.* > from TBOOKING A, TBOOKING B > where A.EVENTTYPE = 3 and B.ID = ( > select min(C.ID) from TBOOKING C > where C.EVENTTYPE = 4 and C.ID > A.ID > ); > > Igor Tandetnik Igor, you are my hero ;-) I've tried a subselect in the join but I've missed the (somehow obvious) min(ID) part. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [PATCH] clean up sqlite3(1) manual
Hi, Marc Espie just imported both the sqlite 3.7.11 library and the sqlite3 command line utility into the OpenBSD base system, so both will likely be an integral part of the upcoming OpenBSD 5.2 release in November 2012. For that reason, i have done minimal cleanup of the sqlite3(1) manual page that i'd like to feed back upstream. I'd be glad if you could review and commit the patch appended below; don't hesitate to ask questions in case anything seems unclear. Here is a rationale explaining the proposed changes: * For the .TH macro, man(7) documents traditionally use the -MM-DD date format as specified in the ISO-8601 standard. * Including excerpts from the roff(7) documentation into individual manuals seems excessive, in particular when most of the explained roff requests are not used. * Trailing whitespace confused some old roff implementations, so it is better to avoid it. * man(7) doesn't allow blank lines except in literal context; better avoid them, even though most modern tools now treat them similar to .PP. On the other hand, do use .PP to start a new logical paragraph. * .PP is implied after .SH and .SS, so drop it at these places. * New sentences should start on new lines. With some tools, this helps to improve inter-sentence spacing. * In high-level man(7) code, avoid the low-level roff(7) requests .br and .sp, in particular at places where they have no effect. * Fix a typo: s/semi-colon/semicolon/. * While .cc is valid roff(7), it makes reading the source harder and some tools do not implement it. In the present case, it is easy to avoid it and to improve portability. * .Bl arguments need to be separated by white space. * Correct spacing around one comma. * Delete one stray line breaking the grammar. * Mark up the -init option in the text. * Use the standard AUTHORS section instead of a custom AUTHOR section. Thank you for maintaining sqlite! Yours, Ingo -- Ingo Schwarze mandoc developer - http://mdocml.bsd.lv/ Index: sqlite3.1 === RCS file: /cvs/src/usr.bin/sqlite3/sqlite3.1,v retrieving revision 1.1 diff -u -p -r1.1 sqlite3.1 --- sqlite3.1 14 Apr 2012 13:33:10 - 1.1 +++ sqlite3.1 14 Apr 2012 14:47:25 - @@ -2,51 +2,36 @@ .\" First parameter, NAME, should be all caps .\" Second parameter, SECTION, should be 1-8, maybe w/ subsection .\" other parameters are allowed: see man(7), man(1) -.TH SQLITE3 1 "Mon Apr 15 23:49:17 2002" +.TH SQLITE3 1 2005-02-24 .\" Please adjust this date whenever revising the manpage. -.\" -.\" Some roff macros, for reference: -.\" .nhdisable hyphenation -.\" .hyenable hyphenation -.\" .ad l left justify -.\" .ad b justify to both left and right margins -.\" .nfdisable filling -.\" .fienable filling -.\" .brinsert line break -.\" .sp insert n+1 empty lines -.\" for manpage-specific macros, see man(7) .SH NAME -.B sqlite3 +.B sqlite3 \- A command line interface for SQLite version 3 - .SH SYNOPSIS .B sqlite3 .RI [ options ] .RI [ databasefile ] .RI [ SQL ] - .SH SUMMARY -.PP .B sqlite3 is a terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats. .B sqlite3 can also be used within shell scripts and other applications to provide batch processing features. - .SH DESCRIPTION To start a .B sqlite3 interactive session, invoke the .B sqlite3 -command and optionally provide the name of a database file. If the -database file does not exist, it will be created. If the database file -does exist, it will be opened. - +command and optionally provide the name of a database file. +If the database file does not exist, it will be created. +If the database file does exist, it will be opened. +.PP For example, to create a new database file named "mydata.db", create a table named "memos" and insert a couple of records into that table: -.sp -$ +.PP +$ .B sqlite3 mydata.db .br SQLite version 3.1.3 @@ -70,54 +55,49 @@ deliver project description|10 lunch with Christine|100 .br sqlite> -.sp - +.PP If no database name is supplied, the ATTACH sql command can be used -to attach to existing or create new database files. ATTACH can also -be used to attach to multiple databases within the same interactive -session. This is useful for migrating data between databases, +to attach to existing or create new database files. +ATTACH can also be used to attach to multiple databases within +the same interactive session. +This is useful for migrating data between databases, possibly changing the schema along the way. - +.PP Optionally, a SQL statement or set of SQL statements can be supplied as -a single argument. Multiple statements should be separated by -semi-colon
[sqlite] search time in FTS3 tables sometimes very long
For our stock management I created a SQLITE 3.5.3 table, containing
around 1,5M entries.
The FTS3 table is created with statement:
CREATE VIRTUAL TABLE volltext using FTS3(referenzcode, code, deut, engl,
ital, sppm, rep, info)
"referenzcode" and "code" are containing part numbers with 10 or 11
digits or letters, f.e. "1401326732D"
"deut", "engl" and "ital" are containing descriptions in different
languages of the same part, max. 80 characters.
"sppm" and "rep" are internal references with up to 6 characters,
describing the part usage ("mech", "elect", ...)
"info" is the biggest field, containing up to some 1000 characters but
also sometimes empty. "info" may contain f.e. a digitalized product
manual or installation manual text.
What is really strange is that FTS3 search phrases like
SELECT referenzcode FROM volltext where volltext match ('installation
manual') are performed really fast within some milliseconds, independent
to the search phrase.
But in general I allow the user to enter a search word in a dedicated
form field (Windows computers), the search starts after entering of each
additional character.
That means:
Entering the word "installation" the search starts after entering the
character "i". Then, after entering the second character "n" the search
field is "in" and starts again. Then the user enters "s" and the search
is interrupted and starts again with search word "ins".
To be able to find not only table entries containing "i", "in", "ins",
"inst", ... there is automatically added the character "*" - in fact the
search phrase is "i*", "in*", "ins"*, "inst*", ...
Now it's very interesting that a search phrase containing at least 4
characters causes a search time of max. some seconds.
But as some parts in our stock are having short names like "P7" or "E5",
sometimes the search phrase is only 2 characters long plus additional "*".
I found out that some character combinations are causing longer search
durations than others.
The search of "E5*" f.e takes 2,7 seconds and is finding 24419 entries.
But if the search f.e. is "F1*" the search takes around 1128,5 seconds
to find 77652 entries.
Other search phrases "vi*" are taking 3,7s for 14803 entries or "ta*"
takes 42,8s for 102189 entries.
A "very good" example is "tm*" with 0,2s for entries.
It's clear to me that the search time in some cases takes longer as in
other cases. Especially I would expect that the search takes as longer
as the amount of found entries is bigger.
But is there anyone who could explain me, why "F1*" takes 1128,5 seconds
search time? Or "F3*" takes 202,8s? What is the reason for such a long
duration?
In my opinion a short search phrase with "*" should be very fast.
By the way, an other example: The same table with same entries is
existing in a standard SQLITE database, without using FTS3.
The search of every phrase, independent to length of the phrase, takes
max. 55 seconds only (after first call; every further search is
performed in max. 10 seconds as the table seems to be in cache then).
Unfortunately this standard search is finding every entry containing the
search phrase, not only the rows where the word is beginning with the
search phrase.
The goal - and reason for my question - is: I tried to use fulltext
search as it should be faster than the standard SQLITE search method,
and in fact it is faster if the search phrase is long enough. But with
short search phrases the FTS3 extension seems to run in troubles.
Is there any possibility to solve this behaviour? Today I tried every
possible search phrase combination with 2 characters only, noticed the
search time and decided to use FTS search only in case the search will
likely take less than 30 seconds. As soon as the search phrase will take
longer than 30 seconds I use the standard SQLITE3 search algorithm.
That's a workaround for today, but I consider someone being here who
could improve the algorithm behaviour of FTS3?
By the way, as I wrote above, the "referenzcode" entry contains mostly
numbers with only some other characters. FTS3 search with digits only
like "13*" or "76*" takes always more than 120 seconds, so the use of
FTS 3 is never possible in that case. It makes only sense to use FTS3 if
the search phrase with digits is at least 4 characters long (f.e. "1403*").
Would be interesting to read your comments about that...
Many thanks!
Best regards
Ingo
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] search time in FTS3 tables sometimes very long
Hi Scott! You're great! I checked the attached modification and found no search taking longer than 20s now! It's a great improvement. I didn't find any other problems, so I will leave the modification in my FTS3 compilation. Many thanks! Ingo Scott Hess schrieb: 2007/12/4 Scott Hess <[EMAIL PROTECTED]>: This seems a little excessive, though. I do see that there's an O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to docListUnion()). I can reasonably make that O(logN), which might help a great deal, if you're hitting it. Not really sure how to tell if you're hitting it, but I'll experiment at my end and see whether I can improve things there. With the attached patch, the time to match against 't*' with the rfc dataset goes from 1m16s to 5s. It passes the tests, but I'll not guarantee that this is what I'll check in. I want to think on it. But let me know if this doesn't help. -scott - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
