'Re: [sqlite] Long execution time since sqlite 3.8'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Long execution time since sqlite 3.8
From:       Richard Hipp <drh () sqlite ! org>
Date:       2014-07-31 14:57:42
Message-ID: CALwJ=MzPba9i1wBF8oxG+H8_XDacVQX5HRXcpKdg-QZTdpjRcg () mail ! gmail ! com
[Download RAW message or body]

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 <ruther1983@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 <ruther1983@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 <ruther1983@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" <drh@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 <ruther1983@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
> > > drh@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
drh@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread] 