Re: [sqlite] Optimize a query
Marco Bambini wrote: This query on a small database sometimes takes more than 40 seconds: select _rowid, public_id, vote_count, status, summary, component, date(date_modified), quickfix from reports where public = 1 AND _rowid IN (select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count DESC I'm no expert, but won't this bit LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count force the query to perform a full table scan regardless of the indexes? What happens to the query speed if you (temporarily) change the LIKE clauses to look for a specific record? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
This is news to me. Why can't SQlite use more than one index? Samuel R. Neff wrote: afaik SQLite will only use one index per table -- Martin Pelletier Informatique / Software Development Infodev Electronic Designers International Inc. Tel : +1 (418) 681-3539, poste /ext. 114 Fax : +1 (418) 681-1209 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
At 16:46 17/04/2007, you wrote: This is news to me. Why can't SQlite use more than one index? Possibly because it's 'SQ *Lite*'? The query optimiser in SQLite is a lot less powerful than in some other SQL databases - but then it's a fraction of the size as well... Instead of having two indices on columns A and B, you need to consider having another index on both columns at once. PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
Martin Pelletier [EMAIL PROTECTED] wrote: This is news to me. Why can't SQlite use more than one index? It can. You just have to tell it to explicitly by restructuring your SQL. As an example, consider this query: SELECT * FROM table1 WHERE a=5 AND b=11; Suppose there are two indices: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); As written, SQLite will only use one of these two indices to perform the query. The choice is arbitrary (unless you have run ANALYZE and SQLite has some information to help it pick the best index.) If you want to use both indices, rewrite the query this way: SELECT * FROM table1 WHERE rowid IN (SELECT rowid FROM table1 WHERE a=5 INTERSECT SELECT rowid FROM table1 WHERE b=11); The optimizer in PostgreSQL will make this change for you automatically and will use a bitmap to implement the IN operator and the INTERSECT. With SQLite, though, you have to type in the expanded version yourself. And because rowids in SQLite are user visible and changeable and can thus be diffuse, SQLite is unable to use bitmaps to optimize the computation. But modulo the bitmap optimization, SQLite gives you all the capabilities of PostgreSQL, you just have to type it in yourself rather than letting the optimizer do it for you. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimize a query
This query on a small database sometimes takes more than 40 seconds: select _rowid, public_id, vote_count, status, summary, component, date (date_modified), quickfix from reports where public = 1 AND _rowid IN (select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%')) order by vote_count DESC Table reports contains 22,605 records Table segments contains 71,413 records I suspect that the slowdown is due to the IN clause used in the query or something else that prevents sqlite from using some optimizations. Anyone can help me to optimize this query? Details follows... TABLES: CREATE TABLE reports (_rowid integer NOT NULL PRIMARY KEY, vote_count integer DEFAULT 0, summary varchar(256), public integer DEFAULT 0, date_created date, locked_by integer DEFAULT 0, public_id varchar (16), component integer, severity varchar(32), priority integer DEFAULT 0, user_id integer, release_note_id integer DEFAULT 0, type integer DEFAULT 0, date_modified timestamp, fixed_version varchar (16), status integer DEFAULT 0, quickfix integer DEFAULT 0, easyfix integer DEFAULT 0) CREATE TABLE segments (_rowid integer not null primary key autoincrement, type integer, public integer default 0, date_created date, content varchar(4096), date_modified date, report_id integer, user_id integer) INDEXES: CREATE INDEX reports_component_idx on reports (component) CREATE UNIQUE INDEX reports_public_id_idx on reports (public_id) CREATE INDEX reports_public_idx on reports (public) CREATE INDEX reports_status_idx on reports (status) CREATE INDEX reports_summary_idx on reports (summary) CREATE INDEX reports_user_id_idx on reports (user_id) CREATE INDEX segments_content on segments (content) CREATE INDEX segments_public on segments (public) CREATE INDEX segments_report_id on segments (report_id) CREATE INDEX segments_type on segments (type) CREATE INDEX segments_user_id on segments (user_id) Thanks a lot, --- Marco Bambini - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimize a query
afaik SQLite will only use one index per table so if you have a where clause WHERE public = 1 and _rowid IN (...) it will use an index on public and not _rowid. Swapping the where clause around should have a significant impact: select _rowid, public_id, vote_count, status, summary, component, date (date_modified), quickfix from reports where _rowid IN ( select distinct r._rowid from reports r, segments s where s.report_id = r._rowid AND r.public = 1 AND s.public = 1 AND (r.summary LIKE '%server%' OR s.content LIKE '%server%') ) AND public = 1 order by vote_count DESC but really the public = 1 where clause on the outer query doesn't look necessary since you already are checking public = 1 in the inner query. Also, formatting SQL statements so they aren't a huge blob of sql will make them easier to read, especially for mailing lists. :-) HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimize a query
Interesting and thanks for that tip. Is there a performance penalty from structuring the query like that? I take it that there will be. RBS Martin Pelletier [EMAIL PROTECTED] wrote: This is news to me. Why can't SQlite use more than one index? It can. You just have to tell it to explicitly by restructuring your SQL. As an example, consider this query: SELECT * FROM table1 WHERE a=5 AND b=11; Suppose there are two indices: CREATE INDEX index1 ON table1(a); CREATE INDEX index2 ON table1(b); As written, SQLite will only use one of these two indices to perform the query. The choice is arbitrary (unless you have run ANALYZE and SQLite has some information to help it pick the best index.) If you want to use both indices, rewrite the query this way: SELECT * FROM table1 WHERE rowid IN (SELECT rowid FROM table1 WHERE a=5 INTERSECT SELECT rowid FROM table1 WHERE b=11); The optimizer in PostgreSQL will make this change for you automatically and will use a bitmap to implement the IN operator and the INTERSECT. With SQLite, though, you have to type in the expanded version yourself. And because rowids in SQLite are user visible and changeable and can thus be diffuse, SQLite is unable to use bitmaps to optimize the computation. But modulo the bitmap optimization, SQLite gives you all the capabilities of PostgreSQL, you just have to type it in yourself rather than letting the optimizer do it for you. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
