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 <[email protected]> *An:* [email protected] *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 <[email protected]> > An: [email protected] > 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" <[email protected]> > > An: "General Discussion of SQLite Database" <[email protected]> > > Betreff: Re: [sqlite] Long execution time since sqlite 3.8 > > > > On Tue, Jul 29, 2014 at 4:55 AM, Michael <[email protected]> > 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 > > [email protected] > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
