On 20 Mar 2012, at 10:45am, Rita <[email protected]> wrote: > I have a single table which has close to 4 millions rows. I write once and > read many times with SELECT. I mainly work with operations like max, min, > and count so I was wondering instead of creating views is there a way I can > get the count() of a table and place it in a SQL variable or table? While > the table is getting populated I would like to build this counter.
You say you write once and do many SELECTs. I assume you mean that you put lots of data in the TABLE first, and then when you're finished making the table you read it. So there is no point in counting the number of entries until you have finished writing. If you are simply INSERTing into the table and never DELETEing, then the row numbers in your table will be one continuous sequence from 1 to the number of rows. A very fast way of getting the number of rows in the table will be SELECT max(rowid) FROM myTable and you can store that value or use that value anywhere. It is much faster for SQLite to find max(column) of an indexed column than it is for SQLite to count the number of rows. For details about rowid, read this page: <http://sqlite.org/autoinc.html> However, if you ever delete rows from the table then max(rowid) is no longer the number of rows in the table since rowid numbers are not reused. > Or have > a SQL variable (if possible) like, count=select count(id) from table; SQL does not have variables. You could make up a table and store a value in it, but that is relatively slow. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
