On 4 Aug 2010, at 6:38pm, Alexander Spence wrote:
> CREATE TABLE [CommentIndex] (
> [CommentKey] TEXT UNIQUE,
> [ThreadPath] TEXT PRIMARY KEY,
> [ParentThreadPath] TEXT,
> [ParentCommentKey] TEXT,
> [NumberOfReplies] INT DEFAULT 0,
> [NumberOfRecommends] INT DEFAULT 0,
> [UserKey] TEXT,
> [Timestamp] INT
> );
> SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' AND UserKey
> IN ('1','2','3') ORDER BY Timestamp
> SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' AND UserKey
> IN ('1','2','3') ORDER BY NumberOfRecommends
> SELECT CommentKey FROM CommentIndex WHERE UserKey IN ('1','2','3') ORDER BY
> Timestamp
> SELECT CommentKey FROM CommentIndex WHERE UserKey IN ('1','2','3') ORDER BY
> NumberOfRecommends
> SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' ORDER BY
> Timestamp
> SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' ORDER BY
> NumberOfRecommends
Instead of "UserKey IN ('1','2','3')" use "UserKey BETWEEN '1' AND '3'"
Then ideal indexes will be
CREATE INDEX PtpUkT ON CommentIndex (ParentThreadPath,UserKey,Timestamp)
CREATE INDEX PtpUkNoR ON CommentIndex
(ParentThreadPath,UserKey,NumberOfRecommends)
CREATE INDEX UkT ON CommentIndex (UserKey,Timestamp)
CREATE INDEX UkNoR ON CommentIndex (UserKey,NumberOfRecommends)
CREATE INDEX PtpT ON CommentIndex (ParentThreadPath,Timestamp)
CREATE INDEX PtpNoR ON CommentIndex (ParentThreadPath,NumberOfRecommends)
> Should I create one index like this? [ParentThreadPath], [UserKey],
> [NumberOfRecommends], [Timestamp] ? Or should I create multiple indices
> targeting the specific queries?
The number of the above you create is a payoff descision. It might not be
worth doing all six, perhaps ignore the Timestamp or NumberOfRecommends ones if
having a smaller database file is important to you. Fewer indexes mean that it
takes less time to do an INSERT and, of course, you have more free disk space.
> Also, does it matter if the index is created with ASC and I order by DESC?
If you mean just the last component (e.g. Timestamp) is reversed, then the
results will be slower. If you find that it makes a significant difference for
you you might want to create indexes for the DESCs, for example
CREATE INDEX PtpUkTd ON CommentIndex (ParentThreadPath,UserKey,Timestamp DESC)
But these will again make the databases file larger. you may find that even
without them your queries run acceptably quickly.
You might want to read
<http://www.sqlite.org/optoverview.html>
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
