Yuzem wrote:
> >
> > Test#1
> > create index tags_index on tags(tag);
> > You should have an index for any fields you query on like this.
> >
>
> Thanks Michael but I don't see any speed improvement:
> create index test on tags(tag);
> select count(distinct tag) from tags;
>
> This is much faster:
> select count(*) from tags;
>
> Am I doing something wrong?
As Michael mentioned, you might be getting killed by string comparisons.
I'm no SQL expert, so gurus are welcome to add to the following without
insulting me.
To beef up Michael's suggestion, try something like this:
CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id));
(BTW, you should consider making movie_id "INTEGER PRIMARY KEY" and then
remove "unique(movie_id)" -- as long as that otherwise makes sense for
your architecture. For SQLite-specific reasons that will probably be
faster.)
CREATE TABLE tagNames(tagId INTEGER PRIMARY KEY, tagName TEXT UNIQUE);
CREATE INDEX tagNamesIdxtagName on tagNames(tagName);
CREATE TABLE tags(movie_id INTEGER,
tagId INTEGER REFERENCES tagNames,
unique(movie_id,tag));
CREATE INDEX tagsIdxTagId on tags(tagId);
Then see how fast it is to ask
SELECT COUNT(*) FROM tagString;
Note: The index tagNamesIdxtagName is there because you'll probably want it
to speed up insertions into 'tags'.
Note: If foreign key checking is on, be careful about deletes on table
"tagNames". Without an index on tags(tagId) a delete of a single row on
tagNames implies a full table scan on tags (to make sure there are no
rows referencing it). That bit me in the past.
Eric
--
Eric A. Smith
The concept is interesting and well-formed, but in order to earn
better than a 'C,' the idea must be feasible.
-- A Yale University management professor in response to Fred Smith's paper
proposing reliable overnight delivery service.
(Smith went on to found Federal Express Corp.)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
