You can _generally_ work around this kind of problem using sub-selects. So instead of:
SELECT x FROM fts_table WHERE fts_table MATCH "y" OR fts_table MATCH "z"; You might write: SELECT x FROM fts_table WHERE rowid IN (SELECT rowid FROM fts_table WHERE fts_table MATCH "y") OR rowid IN (SELECT rowid FROM fts_table WHERE fts_table MATCH "z"); Of course, you could probably phrase it eleven different ways, I'm not suggesting that the above is the most efficient way, just that you can sometimes perturb your query to make things work better :-). Note that the above change would work best if you don't expect many hits. Also, I'm making no representation about the state of SQLite's ability to optimize the original query (you'd rather have written it fts_table MATCH "y OR z" from the get-go). But if the original poster is already thinking of using a virtual table to give access to masses of external data, writing queries in this somewhat more complicated fashion may make a lot of sense. -scott 2008/4/1 Ben Harper <[EMAIL PROTECTED]>: > The only limitation imposed by SQL that I can think of would be > inefficiencies in its query plan builder. That is the part that > figures out how to use the indexes available in the database in order > to execute the SQL query most efficiently. So it really depends on > what type of SQL queries you are going to be running against this huge > DB. > The dangers are easy to evaluate: > Create a quick-and-dirty dummy virtual table mechanism, and respond to > the xBestIndex/xFilter functions. You will notice that for certain > queries, xBestIndex/xFilter does not get used. That means that the > SQLite engine is going to have to walk through your entire table, > evaluating the conditions on each field. This is obviously what you > wish to avoid. As an example, I noticed briefly (I did not investigate > thoroughly) that having an OR condition in a query would prevent the > indexes from being used. That was some time ago, and it was before the > rewrite of the SQL VM, so I don't know if that still applies. You'll > have to investigate your potential queries yourself. A simple query > such as "WHERE myvalue > 100" should definitely invoke the use of your > own indexes. > > Ben > > > > > On Tue, Apr 1, 2008 at 10:12 PM, Aladdin Lampé <[EMAIL PROTECTED]> wrote: > > > > Hi all! > > > > Very often, when people ask this list why they have trouble managing in sqlite a "big table" (50 million lines or more than 10 Go), they are told that sqlite is an embedded database and is not meant to be used for very big databases/tables. > > > > I'm currently in the process of designing a specific, read-only, sqlite "virtual table" in order to enable sqlite to access data stored in an external database which is specially designed to handle very big tables. > > > > My final objective is to be able to easily query a big external table (stored in another database) through the - excellent - sqlite interface. > > > > Now I have this terrible doubt: will the existing sqlite "limitations" for big sqlite tables also apply to my read-only virtual tables? > > > > Thus... am I currently losing my time developing such a "virtual table" with this objective in mind? Or is there a better way to achieve my objective? > > > > Thank you for your help! > > > > _________________________________________________________________ > > Votre contact a choisi Hotmail, l'e-mail ultra sécurisé. Créez un compte gratuitement ! > > http://www.windowslive.fr/hotmail/default.asp > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
