Page 1 of 1

Best way to search SQLite3 Database - Ideas

Posted: Sat Oct 23, 2010 6:33 pm
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

Re: Best way to search SQLite3 Database - Ideas

Posted: Sat Oct 23, 2010 9:07 pm
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

Posted: Sun Oct 24, 2010 6:37 pm
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

Posted: Sun Oct 24, 2010 8:58 pm
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

Posted: Mon Oct 25, 2010 5:14 am
by evstevemd
I understand now,
thanks