Hey guys,
I am writing a comment index database and need some help optimizing the
indices. Here is my table schema and a list of the possible queries. Please
note that they could be Ascending or Descending order by's. This will be used
in an enterprise level social media app that must be optimized for reads.
Should I create one index like this? [ParentThreadPath], [UserKey],
[NumberOfRecommends], [Timestamp] ? Or should I create multiple indices
targeting the specific queries? Also, does it matter if the index is created
with ASC and I order by DESC?
Thanks in advance for your help.
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
________________________________
Please NOTE: This electronic message, including any attachments, may include
privileged, confidential and/or inside information owned by Demand Media, Inc.
Any distribution or use of this communication by anyone other than the intended
recipient(s) is strictly prohibited and may be unlawful. If you are not the
intended recipient, please notify the sender by replying to this message and
then delete it from your system. Thank you.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
