Hi all, I have an application where users can tag news stories with custom tags. I present the latest 20 stories along with all of the tags the user has applied. I wind up with a three-table schema and in order to get all the stories with their tags at once regardless of whether a story has a tag or not, I need to perform a left outer join. In order to get exactly 20 stories, I need to first get the 20 latest story ID's, then do my outer join. I am wondering what the fastest way to achieve this is. Here's the (simplified) schema:
CREATE TABLE stories ( article_id char(100) UNIQUE NOT NULL, postingdate int, title char(255), url char(255) ); CREATE INDEX postid on stories (postingdate,article_id); CREATE TABLE story_tags ( tag_id integer NOT NULL, article_id char(100) NOT NULL); CREATE UNIQUE INDEX unique_tags ON story_tags (article_id,tag_id); CREATE TABLE tags ( id integer primary key autoincrement, name char(15) NOT NULL, user char(15) NOT NULL); CREATE UNIQUE INDEX unique_user_tags ON tags (name,user); The front selection query becomes: select stories.*,tags.name from stories left outer join story_tags on stories.article_id = story_tags.article_id left outer join tags on story_tags.tag_id = tags.id and stories.article_id in (select article_id from stories order by postingdate desc limit 0,20); With a few hundred thousand stories, this query starts to take a few seconds. Thanks Tim _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
