> > On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps <j...@antichoc.net> > wrote: > > > Without using slow triggers or changing the v3 file format there is still > > another possibility which could be implemented relatively easily. All it > > would need is a new pragma (or internal function) like "pragma > > row_count=0/1" and some code. > > > > On invokation, the engine would create a hidden "system" table like > > sqlite_rowcount --similar to sqlite_sequence-- which would initially hold > > row counts for every table in the DB. > > Two obvious places: > > A) In that sqlite_sequence table you mentioned, as an additional column. > Always up-to-date. > > B) In the tables prepared by SQLite ANALYZE. If you want the rowcount > updated, do another ANALYZE.
It's just my two cents, but if you take time to run ANALYZE and you don't care for the value to be synced with the real row count in between two ANALYZEs, why not make a table for your convenience, that will do the same thing you want ANALYZE to do. IE you make a script that will select all names of the tables in database: SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'; And then will perform INSERT INTO my_row_count_cache(table_name, row_count) SELECT 'XXX' AS name, (SELECT COUNT(*) FROM XXX) AS count; for each resulting table. All this can actually be implemented as an SQLite add-on, via virtual table. So for example, instead of doing SELECT COUNT(*) FROM XXX; you could do SELECT count FROM vtb_row_count_cache WHERE table = 'XXX'; Just and idea... Regards, Paul _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
