A little bit more info: SELECT COUNT(*) is implemented as a full table scan, so SQLite is visiting every row in the table, which will get slower and slower as the table gets bigger and the database fragments. This differs from many database engines (which implement an optimization for this) Doing the trigger thing means that it only visits the specific row that contains the count.
-scott On Thu, Apr 3, 2008 at 5:03 AM, P Kishor <[EMAIL PROTECTED]> wrote: > On 4/3/08, Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I am having 40000 records in my Harddisk. > > My Processor speed is 400 Mhz. > > > > For "SELECT COUNT(1) FROM MUSIC ;" its getting more time to display the > > count. > > I have also tried with "SELECT COUNT(*) FROM MUSIC ;This also take more > > time. > > SELECT Count(whatever) takes time. There is no way around that. > > > > Is there any other way we can get the Total number of records. > > If speed of getting total number of records is important to you, keep > a running total in a separate "count_rows" table, and update that > total via TRIGGERs as you insert into, delete from or update your > tables. Then retrieve the total from the count_rows table instead of > using COUNT. Here is the idea... > > CREATE TABLE count_rows (tablename, count_of_rows); > CREATE TRIGGERs AFTER (DELETE|INSERT|UPDATE) ON table > ... UPDATE count_rows SET count_of_rows = ? WHERE tablename = 'table' ... > > > Then, later on, instead of > > SELECT COUNT(*) FROM table > > You do > > SELECT count_of_rows FROM count_rows WHERE tablename = ? > > > Search the archives. Many of your questions will be answered. > > > > > > Please help to solve this. > > > > Thanks & Regards, > > Mahalakshmi > > > > > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
