View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0014658 | MMW 5 | DB / Backup | public | 2018-02-27 11:31 | 2018-10-26 01:21 |
| Reporter | Ludek | Assigned To | |||
| Priority | urgent | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | reopened | ||
| Product Version | 5.0 | ||||
| Target Version | 5.0 | Fixed in Version | 5.0 | ||
| Summary | 0014658: SQLite: Upgrade full-text search engine from FTS3 to FTS5 | ||||
| Description | As suggested by Jiri, we should try upgrade FTS3 to FTS5 as it seems to have some advantages, namely FTS5 should be faster and less memory consuming. | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 2130 | ||||
|
|
Note that it should particularly let us make the MM.DB file smaller. |
|
|
Updated SQLite to v3.23.1 in build 2102 FTS3 -> FTS5 update is TODO |
|
|
Tested db size: Rusty's DB (19k tracks): - without FTS5/FTS3: 190MB - with FTS3: 208 MB - with FTS5: 208 MB My DB (9k tracks): - without FTS5/FTS3: 25MB - with FTS3: 38 MB - with FTS5: 38 MB I run VACUUM after all tests So it doesn't seem to improve DB size so far. |
|
|
The size should be reduced using content tables references: https://www.sqlite.org/fts5.html 4.4.2. External Content Tables |
|
|
Using content table reference: My DB (9k tracks): - without FTS5/FTS3: 25 MB - FTS3: 38 MB - FTS5 (with content table reference): 28 MB - FTS4 (with content table reference): 29 MB Disadvantage of FTS5 is that unlike FTS3/4 it no longer supports '-' operator for not (excluding token) and the meaning of the operator is different (excludes column names). i.e. we can no longer use 'A -B' to find files with A and NOT B Because FTS4 is just extension of FTS3 (more compatible and thoroughly tested) and supports content table references to reduce the DB size then I would prefer using FTS4 for now (MM5) and try FTS5 later (and/or in MMS app). Another reason is that the default FTS5 tokenizer (unicode61) cannot find 'Straße' when searching for 'Strasse'. i.e. we would need to use our tokenizer anyway. => Upgraded to FTS4 in build 2102. Test note: You will need to run 'Manage database' either with: [x] Rebuild full text search index [x] Optimize database (to reduce the db size) or [x] Rebuild database Or newly created DB uses FTS4 |
|
|
Due the incompatibility to MMW library FTS and as we talked offline, we should do Auto Rebuild of FTS on First MM5 start and MMW library is used or wait till we move/switch settings to different location as described in #15162 as then there will be no need to keep existing FTS? Left for you to triage. |
|
|
I've just actually found that the FTS is already re-created during MM4 > MM5 DB upgrade - - because of adding new custom fields (custom6, ..., custom10) I've just added SQLite's VACUUM that reduces the DB size, based on my test MM4 database (9K tracks) reduced from 33 MB to 23 MB during MM4 > MM5 DB upgrade Fixed in 5.0.0.2130 |
|
|
Verified 2130 |
