Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string
In Arabic you have a different direction of the text. Might be that the reason? Sandu Eric Minbiole schrieb am Di., 11. Sep. 2018, 00:23: > > SELECT rowid,text > > FROM table > > WHERE table MATCH 'أعلم*'; > > > > And I have to add this that my data is Arabic text. > > > > This method must find words that contains 'أعلم' but it doesn't. What > > should I do now. > > > > > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work. > Please see my example below. In particular, the first select doesn't have a > *, so it doesn't find the word. The second example includes a trailing *, > so it performs a "starts with" search, and does find the match. (Note that > I simply doubled the letters of your original arabic text to make a longer > word.) > > MacBook-II:Programs eric$ ./sqlite3 > > SQLite version 3.24.0 2018-06-04 19:24:41 > > Enter ".help" for usage hints. > > Connected to a transient in-memory database. > > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc); > > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text'); > > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم'); > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم'; > > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*'; > > 2 > > sqlite> > > > > One possible explanation: You mentioned that you want a "contains" search. > However, the trailing asterisk in your example only results in a "begins > with" search. If you really want a "contains" search, you'll need to put > the * at both the beginning and end of the match word. E.g., "WHERE fts > MATCH '*asdf*'; > > Hope this is helpful. > > ~Eric > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database occasionally very slow for trivial query
On 10 September 2018 at 22:28, Joshua Watt wrote: > BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; > Normally, this query takes no more than 1-3 seconds to complete, > however, on rare occasion this will take an order of magnitude more > (20-30 seconds). > > pragma synchronous = normal; pragma journal_mode = WAL; pragma > wal_autocheckpoint = 100; > I use the small wal_autocheckpoint because the database is read- > mostly, and we would rather have fast readers at the expense of > occasional slow writes. > I don't have much experience with WAL mode, but these two statements jump out at me. Is it simply the auto-checkpoint which ends up taking a long time when the machine is already under heavy i/o load? -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS5 MATCH using "string*" matches just the exact string
> SELECT rowid,text > FROM table > WHERE table MATCH 'أعلم*'; > > And I have to add this that my data is Arabic text. > > This method must find words that contains 'أعلم' but it doesn't. What > should I do now. > > I just tried this in SQLite version 3.24.0 on a Mac, and it seems to work. Please see my example below. In particular, the first select doesn't have a *, so it doesn't find the word. The second example includes a trailing *, so it performs a "starts with" search, and does find the match. (Note that I simply doubled the letters of your original arabic text to make a longer word.) MacBook-II:Programs eric$ ./sqlite3 > SQLite version 3.24.0 2018-06-04 19:24:41 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> CREATE VIRTUAL TABLE fts USING fts5(doc); > sqlite> INSERT INTO fts (rowid, doc) VALUES (1, 'english text'); > sqlite> INSERT INTO fts (rowid, doc) VALUES (2, 'arabic أعلمأعلم'); > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم'; > sqlite> SELECT rowid FROM fts WHERE fts MATCH 'أعلم*'; > 2 > sqlite> > One possible explanation: You mentioned that you want a "contains" search. However, the trailing asterisk in your example only results in a "begins with" search. If you really want a "contains" search, you'll need to put the * at both the beginning and end of the match word. E.g., "WHERE fts MATCH '*asdf*'; Hope this is helpful. ~Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 MATCH using "string*" matches just the exact string
Hi I am new here and I am not an expert on sqlite my question is about match option that I have read from https://sqlite.org/fts5.html but the problem is when I want to not find the exact word so I use : SELECT rowid,text FROM table WHERE table MATCH 'أعلم*'; And I have to add this that my data is Arabic text. This method must find words that contains 'أعلم' but it doesn't. What should I do now. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Database occasionally very slow for trivial query
NB: SELECT COUNT() FROM requires a traversal of the whole table (or index, if one exists for the field) and returns the number of non-NULL entries, whereas SELECT COUNT() FROM invokes a special opcode to retrieve the total number of rows without actually acessing any of them and so is very much faster. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Joshua Watt Gesendet: Montag, 10. September 2018 16:28 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Database occasionally very slow for trivial query Hello, I have seen a strange behavior when using sqlite 3.20.1, and I was hoping someone could help explain it. I have a database with a very simple schema: $ sqlite3 build/cache/bb_persist_data.sqlite3 SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); When our application starts up, it determines if it need to clear our the table. In the event that it does (which is most of the time) it uses the following query: BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Normally, this query takes no more than 1-3 seconds to complete, however, on rare occasion this will take an order of magnitude more (20-30 seconds). The real kicker here, is that I am never adding any rows to the database (e.g. it is always completely empty), so an order of magnitude increase seems unnecessary to erase an already empty table. If it makes any difference, the actual delay occurs when the COMMIT statement is executed, the DELETE FROM goes pretty fast. For reference, the following pragmas are used: pragma synchronous = normal; pragma journal_mode = WAL; pragma wal_autocheckpoint = 100; I use the small wal_autocheckpoint because the database is read- mostly, and we would rather have fast readers at the expense of occasional slow writes. WAL mode is used because we access the database from multiple processes, and we need the occasional write to not block readers. To be completely honest, this problem manifests under heavy I/O load, so I'm not suggesting it is necessarily sure that it is sqlite at fault, but the order of magnitude difference seems a bit extreme. 1) I used to use the rollback journal and didn't really see this problem, is there something about WAL mode that is more sensitive to I/O delay than the rollback journal? 2) Is there something that sqlite is doing "in the background" that might be making this query slow? 3) Are the some settings I could change that might make a difference? 4) Is there some sort of profiling I could enable to help pinpoint (or confirm) that this is indeed due to I/O delay and not something internal to sqlite? For reference, here is the complete log of SQL that the application executes on startup. Only the delay takes a significant amount of time. BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); COMMIT; BEGIN TRANSACTION; SELECT COUNT(key) FROM BB_URI_HEADREVS; COMMIT; BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Thanks all for your time, -- Joshua Watt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database occasionally very slow for trivial query
Hello, I have seen a strange behavior when using sqlite 3.20.1, and I was hoping someone could help explain it. I have a database with a very simple schema: $ sqlite3 build/cache/bb_persist_data.sqlite3 SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); When our application starts up, it determines if it need to clear our the table. In the event that it does (which is most of the time) it uses the following query: BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Normally, this query takes no more than 1-3 seconds to complete, however, on rare occasion this will take an order of magnitude more (20-30 seconds). The real kicker here, is that I am never adding any rows to the database (e.g. it is always completely empty), so an order of magnitude increase seems unnecessary to erase an already empty table. If it makes any difference, the actual delay occurs when the COMMIT statement is executed, the DELETE FROM goes pretty fast. For reference, the following pragmas are used: pragma synchronous = normal; pragma journal_mode = WAL; pragma wal_autocheckpoint = 100; I use the small wal_autocheckpoint because the database is read- mostly, and we would rather have fast readers at the expense of occasional slow writes. WAL mode is used because we access the database from multiple processes, and we need the occasional write to not block readers. To be completely honest, this problem manifests under heavy I/O load, so I'm not suggesting it is necessarily sure that it is sqlite at fault, but the order of magnitude difference seems a bit extreme. 1) I used to use the rollback journal and didn't really see this problem, is there something about WAL mode that is more sensitive to I/O delay than the rollback journal? 2) Is there something that sqlite is doing "in the background" that might be making this query slow? 3) Are the some settings I could change that might make a difference? 4) Is there some sort of profiling I could enable to help pinpoint (or confirm) that this is indeed due to I/O delay and not something internal to sqlite? For reference, here is the complete log of SQL that the application executes on startup. Only the delay takes a significant amount of time. BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS BB_URI_HEADREVS(key TEXT PRIMARY KEY NOT NULL, value TEXT); COMMIT; BEGIN TRANSACTION; SELECT COUNT(key) FROM BB_URI_HEADREVS; COMMIT; BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; Thanks all for your time, -- Joshua Watt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
