Best way to search SQLite3 Database - Ideas  [SOLVED]

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

Best way to search SQLite3 Database - Ideas

Postby evstevemd » Sat Oct 23, 2010 6:33 pm

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?
[Ubuntu 15.04/Windows 10 Pro - GCC/MinGW, CodeLite IDE et al]

utelle
Moderator
Moderator
Posts: 758
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Best way to search SQLite3 Database - Ideas

Postby utelle » Sat Oct 23, 2010 9:07 pm

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

evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2051
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania
Contact:

Postby evstevemd » Sun Oct 24, 2010 6:37 pm

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?
[Ubuntu 15.04/Windows 10 Pro - GCC/MinGW, CodeLite IDE et al]

utelle
Moderator
Moderator
Posts: 758
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

  [SOLVED]

Postby utelle » Sun Oct 24, 2010 8:58 pm

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

evstevemd
Part Of The Furniture
Part Of The Furniture
Posts: 2051
Joined: Wed Jan 28, 2009 11:57 am
Location: United Republic of Tanzania
Contact:

Postby evstevemd » Mon Oct 25, 2010 5:14 am

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?
[Ubuntu 15.04/Windows 10 Pro - GCC/MinGW, CodeLite IDE et al]


Return to “Database Related”

Who is online

Users browsing this forum: No registered users and 2 guests