On 2/20/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote: > > Suppose I have 1280009 rows in table. > CREATE TABLE TableA > ( > ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > column1 VARCHAR (50) NOT NULL, > column2 VARCHAR (50) NOT NULL, > column3 TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP), > column4 VARCHAR (128) NULL, > column5 VARCHAR (255) NULL, > column6 VARCHAR ( 128 ) NULL, > column7 TEXT NULL, > column8 TEXT NULL > ) > > I have select query which looks like > select ID from TableA where column2 like '%test%' or column4like > '%test%' or column5 like '%test%' or column6 like '%test%' or column7 > like '%test%' or column8 like '%test%' order by column3 desc limit > 100000; > > So limit 100000 will search only through 100000 records in table or > gives (100000)number of rows returned in the result.According to > documenation The LIMIT clause places an upper bound on the number of > rows returned in the result. > > Instead of searching the entire table at once I would like to search > 100000 rows from table ,so that my search will be little faster. >
right... but *which* 100,000 rows do you want to search out of the 1,280,009 you have? How can the db guess that for you unless you tell it? How about, select ID from TableA where ID <= 100000 AND column2 like '%test%' or column4 like '%test%' or column5 like '%test%' or column6 like '%test%' or column7 like '%test%' or column8 like '%test%' order by column3 desc; But, of course, then only the first 100k rows are searched. You could repeat this in a loop in your application. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
