On Fri, Feb 13, 2009 at 8:23 AM, He Shiming <heshim...@gmail.com> wrote: > Hi, > > I'm working on this project. It puts a lot of files into a single sqlite > database (including the actual file content, the program is designed to > store the actual content) for search. When a search command is issued, I run > a SELECT command on the FILE_INFO table to get stuff like file name, size > and date. Then I run a background thread to obtain the actual file content > from FILE_CONTENT table. The syntax of the query to obtain the file content > is quite silly. Since I have all the row IDs in the first query, I ran a > query like this: SELECT bin_content FROM FILE_CONTENT WHERE id in > (1,2,3,4...); > > The query is built by sprintf with a for loop, obviously. Sometimes, there > can be several hundreds or thousands of numbers in those brackets. > > I couldn't help but thinking there might be a way to improve this query > syntax. > > I could mix the two query into one using LEFT JOIN. But that'll slow things > down, and I wanted some quick response before seeing the actual content. It > looks like other options will also slow down the second query. > > So I would like to know if it's okay to have queries running this way? Is it > typically done this way or can I improve it?
if the FILE_CONTENT is plain text, you will do well to look at full text search (FTS3). -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison Wisconsin United States. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
