Test#1 create index tags_index on tags(tag); You should have an index for any fields you query on like this.
Test#2 Normalize your tags into a separate table so you then store the rowid in your tags table instead of the string. Your compares will be notably faster using integers rather than strings for more fancy queries. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Yuzem [naujnit...@gmail.com] Sent: Monday, February 07, 2011 9:26 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Speed up count(distinct col) 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
