BareFeetWare-2 wrote: > > Oh, I see. I was counting the total number of each unique capacity > (including director, writers), but you want the number of each unique > (capacity, person). No problem. We'll just add a People column to the > statistics, and change the uniqueness constraint. > > Replace my earlier "Capacity Statistics" and triggers with this: >
That should work, if I understand it correctly now I can count directors with: SELECT count(*) FROM "Capacity People Statistics" WHERE ID = 'directors'; Instead of: SELECT count(distinct People_ID) FROM "Movie People" WHERE Capacity_ID = 'directors'; It is similar to using different tables with only the IDs. Instead of using a table for directors another for writers, etc... this is specified in a column, it is slower but it uses only one table. If I use different tables the result is instant, I don't know if it will be much faster to count from "Capacity People Statistics" than counting from "Movie People". Another thing: I don't understand the purpose of the Count column in table "Capacity People Statistics" Simon Slavin-3 wrote: > > So if I deleted one record and created another you wouldn't spot it ? > Yes, any change to the database will result in a complete refresh of the cache. Simon Slavin-3 wrote: > > Your process is trustworthy only when you are certain that the database > file is not currently open. If there's a chance that some application may > be modifying the file when you check these things then the results you get > may not be up-to-date. > Yes but what can I do about it, the same happens if I open a text document that it is already open in another text editor. I could check if there is a journal file but I don't know if it is necessary since I am not keeping any connection open, I try to make the connections as short as possible and if I get some data that it isn't up to date I don't see much problem. Something that I just realized, comparing INTEGER columns doesn't seems faster than comparing TEXT columns, this: SELECT count(distinct ROWID) FROM keywords; Result: 83513 Is slower than this: SELECT count(distinct keywords) FROM keywords; Result: 17321 -- View this message in context: http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30900999.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
