Android Example SQLite Full Text Search on a Large Database and Display on an xClv

The primary objective of this cross-platform B4XPages SQLite FTS example is to show the syntax used for Full Text Search queries in SQLite. The database consists of close to 400000 records. It is created from scratch in the project via the imported text file Words.txt. The first time the project is run it will take a few seconds to create and convert the text file to the database, but subsequent runs become a smooth sailing.
The project demonstrates how fast data can be searched from an FTS4 table. Please note that I only tested in mostly B4A and also B4J. I do not use B4i.
The B4XPages project features are:
  1. Import data from a text file using StringUtils
  2. Syntax to create the database and table
  3. Syntax to select the data by typing a search string in a B4XFloatTextField and display of time duration for each search.
  4. Display the found records on an xClv with lazy loading.
  5. I also show some commented samples of select queries used in FTS4
  6. Although it uses Word.txt text file for the data, it can easily be changed to a different text file. Download of words.txt can be done from post #12 of this thread and the file needs to be added to assets folder. I left it out because of its large size: https://www.b4x.com/android/forum/threads/dictionary-application-where-is-a-good-start.133326/
 

Attachments

  • SQLiteFullTextSearchForForum.zip
    20.2 KB · Views: 356

William Lancee

Well-Known Member
Licensed User
Longtime User
Wow. I guess I'll have get a faster Tablet. Mine is old (Tab S2).
Since all the work is being done by SQLite, the debug mode is almost the same. Latest test .73 (Release), .77 (debug).
 

bsnqt

Active Member
Licensed User
Longtime User
Hi Mahares, it is really strong search and very fast. Thank you.
 

Peter Simpson

Expert
Licensed User
Longtime User
Daaaaaaaaaaaaaaaaaam @Mahares,
I've said it before and I'll gladly say it again, but you really are the man when it comes to SQLite (which I really should use more, but have no reason to do so). This search is ridiculously fast and I personally wasn't expecting the fast results that I got in debug mode.

On my test phone in debug mode I'm getting 'Found 4558 records for PRE in: .21 secs', on my development laptop (currently running on battery) I'm getting 'Found 4558 records for PRE in: .11 secs'.

Excellent results 👍
 
Last edited:
Top