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
Best way to search SQLite3 Database - Ideas Topic is solved
- evstevemd
- 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
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?
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
Re: Best way to search SQLite3 Database - Ideas
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.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?
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
- Posts: 2409
- Joined: Wed Jan 28, 2009 11:57 am
- Location: United Republic of Tanzania
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
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?
Citizens: What it is his honor?
Chief Justice:Our president is an atheist, who will he swear to?
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: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?
Code: Select all
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Code: Select all
WHERE content LIKE '%linux%'; /* 22.5 seconds */
WHERE content MATCH 'linux'; /* 0.03 seconds */
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