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