Re: [sqlite] Query optimization help
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]
Re: [sqlite] Query optimization help
"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]
[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]
[sqlite] Re: Using SQLite
On Sunday 01 February 2004 04:06 pm, Demitri Muna wrote: > Hello, Hi, Demitri - > I need to embed a database into my application, and was referred to > SQLite. I narrowed down my options to it and PostgreSQL (which I am not > completely sure I can embed into an app). One thing that I need to be > able to do is regular expression searching, something that PostgreSQL > can do, but I don't think SQLite can. What is the best way to work > around this? I don't think PostgreSQL is embeddable, but maybe I'm wrong. It's certainly much bigger than SQLite. You are correct that SQLite doesn't have built-in regexp search, but you can easily add your own function and make SQLite use it. > The only solution that I can see is to keep all of my data in memory. > (I expect I'll have upwards of 10,000 records, 1K each on average.) I That's only 10 Meg, which isn't really too bad on modern machines. But if you think the db may grow, or the record size change, you might be better off leaving it on disk. > Has anyone else run into this? Not this specifically, but I have added my own functions to SQLite at runtime, and this is a great feature to use and will address your first concern.0 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Using SQLite
Hello, I am developing an application (with Cocoa, on Mac OS X, but that's not important), and I have a few questions. I need to embed a database into my application, and was referred to SQLite. I narrowed down my options to it and PostgreSQL (which I am not completely sure I can embed into an app). One thing that I need to be able to do is regular expression searching, something that PostgreSQL can do, but I don't think SQLite can. What is the best way to work around this? The only solution that I can see is to keep all of my data in memory. (I expect I'll have upwards of 10,000 records, 1K each on average.) I can do regexp searches there, and use the database for saving the data and simple searches. Although, if the records are already in memory, I might as well search there. But that's more coding for me. Alternatively, if PostgreSQL can be embedded, it might be a better solution since all searches can be performed by the database. Has anyone else run into this? Thanks for your help! Cheers, Demitri - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
