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

Reply via email to