BareFeetWare-2 wrote: > > In that case, you should cache the counts in a separate table or two. That > has a negligible overhead when you add a movie (which is infrequent), and > basically no overhead when viewing (which is frequent). I am doing that but in the application level, the down side is that I am updating the cache on any change, I check the modified time of the database.
BareFeetWare-2 wrote: > > Do that with triggers (which are a good thing in this context), to change > the relevant count when an insert, delete or update occurs in the "Movie > People" table. Something like: > I think, not sure, that there will be a problem with that code. I am not an expert so I had to stare that code some time to understand it. If I am correct you are adding and subtracting 1 on every insert or delete. The problem is that an insert may not be unique so before augmenting the counter you have to check if it is unique and therefore you would have to run a count(distinct col) on every insert which would be overkill. That's the beauty of using a additional table. I make the column unique in the the extra table and then on any insert in "movies people" I insert in for example the directors table and any duplicate will be automatically rejected. The same problem exist on delete, the deleted entry may not be unique. For example: movie1|director|people1 movie2|director|people1 -- View this message in context: http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30897526.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
