Best way to search SQLite3 Database - Ideas Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2409
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Best way to search SQLite3 Database - Ideas

Post by evstevemd »

I know basic SQL for searching like LIKE and WHERE. I was reading somewhere and I saw MATCH..AGAINST but I saw it requires some kind of strange FST3 table haven't see.

Okay, my question goes like this, How to search effectively and optimum?
Thanks
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Best way to search SQLite3 Database - Ideas

Post by utelle »

evstevemd wrote:I know basic SQL for searching like LIKE and WHERE. I was reading somewhere and I saw MATCH..AGAINST but I saw it requires some kind of strange FST3 table haven't see.

Okay, my question goes like this, How to search effectively and optimum?
To allow full-text searches SQLite has the FTS3 extension. The acronym FTS stands for "full-text search" and the number 3 is kind of a version number.

See http://www.sqlite.org/fts3.html for a detailed description of this extension and how to use it.

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2409
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

now, I know this question is now heading more of SQL than SQLite, but bear with me please. What difference full search makes compared to normal LIKE, WHERE and HAVING clauses?
Thanks
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

evstevemd wrote:now, I know this question is now heading more of SQL than SQLite, but bear with me please. What difference full search makes compared to normal LIKE, WHERE and HAVING clauses?
WHERE and HAVING clauses allow to specify conditions by which the result set is filtered. The HAVING clause is dedicated especially to specify conditions for aggregate expressions. Example:

Code: Select all

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
If your search condition contains a LIKE expression this usually means that the database management system has to perform a full table scan since it can't use any indices. If you use the full-text search extension on inserting text content all words of the content are indexed and that makes searches extremly fast. Example (taken from the SQLite web site):

Code: Select all

WHERE content LIKE '%linux%'; /* 22.5 seconds */
WHERE content MATCH 'linux';  /* 0.03 seconds */
Additionally the full-text search extension allows to specify much more complex search expressions without sacrificing search speed.

Whether using FTS3 makes sense for your application depends on your specific needs and the amount of data in your database. If you only have a small amount of data and need only simple LIKE expressions you probably won't need FTS3.

Regards,

Ulrich
User avatar
evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2409
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania

Post by evstevemd »

I understand now,
thanks
Chief Justice: We have trouble dear citizens!
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
Post Reply