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]
-----------------------------------------------------------------------------
