Hi. I have this tables: CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id)); CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));
I have many movies by tag and many tables like "tags" (keywords, countries, languages, genres, etc..) I can count different movies very fast with: SELECT count(*) FROM movies; But the other tables are much slower depending on the size of the table: SELECT count(distinct tag) FROM tags; My solution is to create an additional table "count_tags" and then every time a tag is added to table "tags" a trigger adds the tag to "count_tags", I need also another trigger to remove the tag, then I can do: SELECT count(*) FROM count_tags; This solution implies one additional table and two triggers by each table. I wanted to know if there is a simpler solution, maybe by using indexes. Thanks in advance. -- View this message in context: http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30864622.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
