Hi Richard,
try this
SELECT
DISTINCT
p.poster_id AS has_posted,
t.id,
t.subject,
t.poster,
t.posted,
t.last_post,
t.last_post_id,
t.last_poster,
t.num_views,
t.num_replies,
t.closed,
t.sticky,
t.moved_to
FROM
topics AS t , posts AS p
WHERE
t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,2761, 2745)
and
t.id=p.topic_id
AND
p.poster_id=2
There a post from Dr R about how SQLite works out joins. The above should be the
final result it I read it right. You might want to also try using group by instead of
DISTINCT
In other DB it is faster.
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
regards
Greg
----- Original Message -----
From: Rickard Andersson
To: [EMAIL PROTECTED]
Sent: Monday, February 02, 2004 2:00 PM
Subject: [sqlite] Query optimization help
I'm having some performance problems with queries looking like the
following:
SELECT DISTINCT p.poster_id AS has_posted, t.id, t.subject, t.poster,
t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views,
t.num_replies, t.closed, t.sticky, t.moved_to
FROM topics AS t
LEFT JOIN posts AS p
ON t.id=p.topic_id AND p.poster_id=2
WHERE t.id IN(3082, 2467, 3076, 3055, 3016, 3019, 2509, 2788, 2804, 2396,
2761, 2745)
The above query takes a full second to complete on my P3-450. The database
contains approx. 1200 topics and 8000 posts. All relevant columns have
indices (t.id, p.topic_id, p.poster_id). Do you believe there is a way to
speed it up?
--
Rickard Andersson
arpen_at_home_dot_se
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
