I can't find any difference in the query plans for 3.7.17 and 3.8.5. Can you send me the database file (as an attachment to a private email) so that I can run experiments on the original content?
On Thu, Jul 31, 2014 at 10:06 AM, Michael <ruther1...@gmx-topmail.de> wrote: > I also tried to remove the views of some queries and create index. But > nothing worked so far. > Anyway it couldn't be the reason for the "parser stack overflow" of my > other question. > Is the information I sent sufficient? > > *Gesendet:* Dienstag, 29. Juli 2014 um 15:17 Uhr > *Von:* Michael <ruther1...@gmx-topmail.de> > *An:* sqlite-users@sqlite.org > *Betreff:* Re: [sqlite] Long execution time since sqlite 3.8 > CREATE TABLE android_metadata (locale TEXT); > CREATE TABLE tbl_lib_filters(UUID TEXT NOT NULL PRIMARY KEY, template > TEXT NOT NULL, library TEXT NOT NULL, rules TEXT); > CREATE TABLE tbl_library(UUID TEXT NOT NULL PRIMARY KEY, TITLE TEXT, > ICON_URI TEXT, DESCCRIPTION TEXT, REMOVED INTEGER NOT NULL, > sortTemplateUUID TEXT, groupTemplateUUID TEXT, sortDirection INTEGER, > templateVersion INTEGER, googleDocId TEXT, googleDocWorksheet TEXT, > googleDocNeedSync INTEGER, googleDocTemplateVersion INTEGER, > picasaAlbumId TEXT, public_id TEXT, lib_alias TEXT, allowed_users TEXT, > private_lib INTEGER, need_update_pub_template INTEGER, type INTEGER, > group_id INTEGER, google_sync_time INTEGER, public_time INTEGER, > protected INTEGER, encripted INTEGER, lib_order INTEGER, tile_columns > INTEGER, tile_color INTEGER, edit_time INTEGER, filter_uuid TEXT, > tile_text_color INTEGER, cloud_storage TEXT, cloud_folder_id TEXT, > lock_edit INTEGER, template_gd_file_id TEXT, entry_pages TEXT, > sort_options TEXT); > CREATE TABLE tbl_library_item(UUID TEXT NOT NULL PRIMARY KEY, LIB_UUID > TEXT, REMOVED INTEGER NOT NULL, creation_date INTEGER, REMOVED_TIME > INTEGER, VIEW_TIME INTEGER, FAVORITE INTEGER, EDIT_TIME INTEGER, FTS3_ID > INTEGER); > CREATE TABLE tbl_flex_content2(id INTEGER PRIMARY KEY AUTOINCREMENT, > stringContent TEXT, realContent REAL, intContent INTEGER, ownerUUID TEXT > NOT NULL, templateUUID TEXT NOT NULL); > CREATE TABLE tbl_filter_profiles(UUID TEXT NOT NULL PRIMARY KEY, library > TEXT NOT NULL, filter_name TEXT); > CREATE TABLE tbl_flex_template(UUID TEXT NOT NULL PRIMARY KEY, title > TEXT, type_code TEXT NOT NULL, usage INTEGER NOT NULL, number INTEGER > NOT NULL, encripted INTEGER, req INTEGER, LIB_UUID TEXT, stats TEXT, > hint TEXT, depends TEXT, display_title INTEGER); > CREATE TABLE tbl_gdocs_item_handlers(item_uuid TEXT NOT NULL PRIMARY > KEY, lib_uuid TEXT NOT NULL, gdocs_record_id TEXT NOT NULL, remove_flag > INTEGER, etag TEXT NOT NULL, edited INTEGER); > CREATE TABLE tbl_picasa_images(picasa_url TEXT NOT NULL, local_uri TEXT > NOT NULL, lib_uuid TEXT NOT NULL); > CREATE TABLE tbl_pub_item_handlers(item_uuid TEXT NOT NULL PRIMARY KEY, > lib_uuid TEXT NOT NULL, pub_id TEXT NOT NULL, remove_flag INTEGER, > version INTEGER, edited INTEGER); > CREATE TABLE tbl_groups(id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT > NULL, sys_code TEXT); > CREATE TABLE tbl_master_hash (pass_hash TEXT NOT NULL PRIMARY KEY); > CREATE TABLE tbl_icon_storage(id INTEGER PRIMARY KEY AUTOINCREMENT, icon > BLOB); > CREATE TABLE tbl_field_email_format_2 (template_uuid TEXT NOT NULL, > lib_uuid TEXT NOT NULL, new_line INTEGER, ex_options TEXT, > colon_after_name INTEGER, email_template INTEGER, send_by_email INTEGER); > CREATE TABLE tbl_reminders3(id INTEGER PRIMARY KEY AUTOINCREMENT, > rem_minutes INTEGER, rem_time INTEGER); > CREATE TABLE tbl_email_templ (id INTEGER PRIMARY KEY > AUTOINCREMENT,lib_uuid TEXT NOT NULL,title TEXT); > CREATE TABLE tbl_cloud_files(cloud_url TEXT NOT NULL, local_uri TEXT NOT > NULL, cloud_id TEXT NOT NULL, lib_uuid TEXT NOT NULL); > CREATE VIRTUAL TABLE library_fts3 USING fts3(); > CREATE TABLE 'library_fts3_content'(docid INTEGER PRIMARY KEY, > 'c0content'); > CREATE TABLE 'library_fts3_segments'(blockid INTEGER PRIMARY KEY, block > BLOB); > CREATE TABLE 'library_fts3_segdir'(level INTEGER,idx INTEGER,start_block > INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY > KEY(level, idx)); > CREATE TABLE category_alias (id integer PRIMARY KEY, category_id > integer, name string, Unique (name)); > CREATE TABLE disks_bez (id integer PRIMARY KEY, name string, bez string, > Unique (name)); > CREATE TABLE item_ref(id integer PRIMARY KEY,item1_id integer, > item1_type_id integer, item2_id integer, item2_type_id integer, unique > (item1_id, item1_type_id, item2_id, item2_type_id)); > CREATE TABLE links(id integer PRIMARY KEY,name text,mediatype > text,source text,mirror text,dateAdded datetime,zone integer default 3, > pid integer, fid integer, votes integer default 0 not null, rating > integer default 0 not null, unique (source)); > CREATE TABLE zone_bez (id integer PRIMARY KEY, zone integer, bez string, > Unique (zone)); > CREATE TABLE zones (id integer PRIMARY KEY, zones integer, zone integer, > Unique (zones,zone)); > CREATE TABLE category_item(id integer PRIMARY KEY,category_id integer > not null,item_id integer not null,item_type_id integer not null,UNIQUE > (category_id,item_id,item_type_id)); > CREATE TABLE history(item_id integer, item_type_id integer, search_term > text, categories text, ex_categories text, zones integer, timestamp > datetime, command text, parameters text, output text); > CREATE TABLE category(id integer PRIMARY KEY,name text,parent integer, > zone integer,UNIQUE (name)); > CREATE TABLE files(id integer PRIMARY KEY,name text,mediatype > text,disksource text,inode integer,bdisksource text,binode > integer,source text,zone integer default 3, votes integer default 0 not > null, rating integer default 0 not null, UNIQUE (disksource,inode)); > CREATE TABLE UUID_infosystem(UUID TEXT NOT NULL PRIMARY KEY,item_id > integer not null,item_type_id integer not null); > CREATE TABLE infos(id integer PRIMARY KEY,name text,text text,zone > integer default 3, dateAdded datetime, lastModified datetime, votes > integer default 0 not null, rating integer default 0 not null, > expiration datetime, exp_action integer, date dateTime); > CREATE TABLE tbl_charts(id INTEGER PRIMARY KEY AUTOINCREMENT, title > TEXT, libraryUUID TEXT, options TEXT, type TEXT, filterUUID TEXT); > CREATE INDEX idx_library_removed ON tbl_library ( REMOVED ); > CREATE INDEX idx_library_item_lib ON tbl_library_item ( LIB_UUID ); > CREATE INDEX idx_library_item_removed ON tbl_library_item ( REMOVED ); > CREATE INDEX idx_library_item_fts3 ON tbl_library_item ( FTS3_ID ); > CREATE INDEX idx_flex_content_owner ON tbl_flex_content2 ( ownerUUID ); > CREATE INDEX idx_flex_content_temp ON tbl_flex_content2 ( templateUUID ); > CREATE INDEX idx_filters2_library ON tbl_filter_profiles ( library ); > CREATE INDEX idx_flex_template_lib ON tbl_flex_template ( LIB_UUID ); > CREATE INDEX idx_picasa_lib_uuid ON tbl_picasa_images ( lib_uuid ); > CREATE INDEX idx_cloud_files_lib_uuid ON tbl_cloud_files ( lib_uuid ); > CREATE INDEX idx_cloud_files_local_uri ON tbl_cloud_files ( local_uri ); > CREATE VIEW category_link as select category_id, item_id from > category_item where item_type_id=2; > CREATE VIEW category_info as select category_id, item_id from > category_item where item_type_id=1; > CREATE VIEW v_infos as select * from infos; > CREATE VIEW category_file as select category_id, item_id from > category_item where item_type_id=3; > CREATE VIEW v_links as select *,dateAdded as date from links; > CREATE VIEW mv_name AS > select FTS3_ID,stringContent as name from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='name'; > CREATE VIEW mv_text AS > select FTS3_ID,stringContent as text from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='text'; > CREATE VIEW mv_zone AS > select FTS3_ID,intContent as zone from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='zone'; > CREATE VIEW mv_dateAdded AS > select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch', > 'localtime') as dateAdded from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='dateAdded'; > CREATE VIEW mv_date AS > select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch', > 'localtime') as date from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='date'; > CREATE VIEW mv_expiration AS > select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch', > 'localtime') as expiration from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='expiration'; > CREATE VIEW mv_exp_action AS > select FTS3_ID, intContent as exp_action from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='exp_action'; > CREATE VIEW mv_lastModified AS > select FTS3_ID, datetime(substr(EDIT_TIME,1,10), 'unixepoch', > 'localtime') as lastModified from tbl_library_item; > CREATE VIEW mv_votes AS > select FTS3_ID,intContent as votes from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='votes'; > CREATE VIEW mv_rating AS > select FTS3_ID,intContent as rating from > tbl_flex_content2,tbl_library_item,tbl_flex_template where > ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID > and title='rating'; > > > Gesendet: Dienstag, 29. Juli 2014 um 15:12 Uhr > > Von: Michael <ruther1...@gmx-topmail.de> > > An: sqlite-users@sqlite.org > > Betreff: Re: [sqlite] Long execution time since sqlite 3.8 > > > > Ok that's the output of sqlite_stat1: > > > > tbl|idx|stat > > android_metadata||1 > > tbl_flex_template|idx_flex_template_lib|10 10 > > tbl_flex_template|sqlite_autoindex_tbl_flex_template_1|10 1 > > zones|sqlite_autoindex_zones_1|84 6 1 > > history||5870 > > tbl_flex_content2|idx_flex_content_temp|20010 2001 > > tbl_flex_content2|idx_flex_content_owner|20010 10 > > tbl_library|idx_library_removed|1 1 > > tbl_library|sqlite_autoindex_tbl_library_1|1 1 > > tbl_lib_filters|sqlite_autoindex_tbl_lib_filters_1|4 1 > > tbl_filter_profiles|idx_filters2_library|4 4 > > tbl_filter_profiles|sqlite_autoindex_tbl_filter_profiles_1|4 1 > > category_item|sqlite_autoindex_category_item_1|6827 20 2 1 > > library_fts3_segdir|sqlite_autoindex_library_fts3_segdir_1|28 10 1 > > library_fts3_segments||254 > > infos||1293 > > tbl_groups||1 > > disks_bez|sqlite_autoindex_disks_bez_1|11 1 > > tbl_library_item|idx_library_item_fts3|2000 1 > > tbl_library_item|idx_library_item_removed|2000 2000 > > tbl_library_item|idx_library_item_lib|2000 2000 > > tbl_library_item|sqlite_autoindex_tbl_library_item_1|2000 1 > > category|sqlite_autoindex_category_1|416 1 > > library_fts3_content||2000 > > zone_bez|sqlite_autoindex_zone_bez_1|4 1 > > category_alias|sqlite_autoindex_category_alias_1|114 1 > > item_ref|sqlite_autoindex_item_ref_1|93 2 2 1 1 > > links|sqlite_autoindex_links_1|2762 2 > > > > and the schema attached > > > > > > > Gesendet: Dienstag, 29. Juli 2014 um 12:52 Uhr > > > Von: "Richard Hipp" <d...@sqlite.org> > > > An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > > Betreff: Re: [sqlite] Long execution time since sqlite 3.8 > > > > > > On Tue, Jul 29, 2014 at 4:55 AM, Michael <ruther1...@gmx-topmail.de> > > wrote: > > > > > > > > > > > Should I post the EXPLAIN? > > > > > > > > > > No. You should post the database schema and the content of the > > > sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables if such tables > > exist. > > > > > > > > > -- > > > D. Richard Hipp > > > d...@sqlite.org > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users