"Rickard Andersson" <[EMAIL PROTECTED]> writes: > 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?
For fun, you might try modifying the query as follows to see how much, if any, faster it is. The "IN" operator is a bit difficult to optimize, although I had thought that sqlite did a pretty good job of it... 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 = 3082 OR t.id = 2467 OR t.id = 3076 OR t.id = 3055 OR t.id = 3016 OR t.id = 3019 OR t.id = 2509 OR t.id = 2788 OR t.id = 2804 OR t.id = 2396 OR t.id = 2761 OR t.id = 2745) --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
