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?
If you have separate indices on p.topic_id and p.poster_id, SQLite will only use one or the other, and it has a 50% chance of choosing the wrong one. I suggest you try it with a single index that includes both p.topic_id and p.poster_id. Like this:
CREATE TABLE p_idx ON p(topic_id, poster_id);
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
