I've searched Banshee's source code and it seems 'CoreCache' is a TEMP
table according to some comments in the code but I have no clue where
it's actually defined, there is no 'CREATE TABLE' statement in the
code for that particular table (I've searched even with TEMP or
TEMPORARY keywords, nothing). Then I've googled around and found this
definition:
CREATE TABLE CoreCache (
OrderID INTEGER PRIMARY KEY,
ModelID INTEGER,
ItemID INTEGER);
On this URL:
https://mail.gnome.org/archives/banshee-list/2009-January/msg00174.html
Anyway I have attached the .fullschema output.
On Thu, Oct 23, 2014 at 3:09 AM, Richard Hipp <d...@sqlite.org> wrote:
> Thanks for sending in the performance regression report.
>
> On Wed, Oct 22, 2014 at 1:51 PM, Tomislav Ljubej <spor...@gmail.com> wrote:
>
>> Hello,
>>
>> There is a performance regression in the media player Banshee on the
>> query that is in the attachment. The query takes about 13 seconds on
>> my i7 4770 processor. I tried the latest stable version before the
>> current one which is 3.8.6.1 and the performance regression doesn't
>> happen, the query completes in less than half a second.
>>
>> The query is run 3 times at Banshee startup so it slows down the
>> startup of the application significantly.
>>
>> Also attached is the .schema output for the database Banshee uses. I'm
>> not sure why the 'CoreCache' table isn't mentioned in the schema but
>> I'm pretty sure it's actually referring to 'CoreCacheModels', I got
>> the query from Banshee's debug output so it may not reflect the actual
>> names in the database.
>>
>
> Several problems:
>
> (1) Your "query" is actually two statements: a DELETE followed by an
> INSERT. The INSERT is fed from a SELECT. Would I be correct in assuming
> that the SELECT is the problem?
>
> (2) You are right that CoreCache is not in the schema, so the SELECT is not
> valid. I tried changing the name of the CoreCache table in the SELECT into
> CoreCacheModels, but then it complains that CoreCacheModels has no ItemID
> column.
>
> (3) After you determine the correct schema and query, it would be helpful
> to us if you can send us the output of ".fullschema". The ".fullschema"
> output includes the sqlite_stat1 and sqlite_stat4 tables (if they exist)
> which contain histogram data used by the query planner. Those table (if
> they exist) are needed by us in order to reproduce the behavior your are
> observing.
>
> (4) If you are able to send us your complete database file, perhaps by
> private email, that would be even more helpful, but is not absolutely
> necessary.
>
>
>>
>> --
>> Tomislav Ljubej
>>
>> _______________________________________________
>> 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
--
Tomislav Ljubej
CREATE TABLE CoreConfiguration (
EntryID INTEGER PRIMARY KEY,
Key TEXT,
Value TEXT
);
CREATE TABLE CorePrimarySources (
PrimarySourceID INTEGER PRIMARY KEY,
StringID TEXT UNIQUE,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
);
CREATE TABLE CoreTracks (
PrimarySourceID INTEGER NOT NULL,
TrackID INTEGER PRIMARY KEY,
ArtistID INTEGER,
AlbumID INTEGER,
TagSetID INTEGER,
ExternalID INTEGER,
MusicBrainzID TEXT,
Uri TEXT,
MimeType TEXT,
FileSize INTEGER,
BitRate INTEGER,
SampleRate INTEGER,
BitsPerSample INTEGER,
Attributes INTEGER DEFAULT 5,
LastStreamError INTEGER DEFAULT 0,
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
TrackNumber INTEGER,
TrackCount INTEGER,
Disc INTEGER,
DiscCount INTEGER,
Duration INTEGER,
Year INTEGER,
Genre TEXT,
Composer TEXT,
Conductor TEXT,
Grouping TEXT,
Copyright TEXT,
LicenseUri TEXT,
Comment TEXT,
Rating INTEGER,
Score INTEGER,
PlayCount INTEGER,
SkipCount INTEGER,
LastPlayedStamp INTEGER,
LastSkippedStamp INTEGER,
DateAddedStamp INTEGER,
DateUpdatedStamp INTEGER,
MetadataHash TEXT,
BPM INTEGER,
LastSyncedStamp INTEGER,
FileModifiedStamp INTEGER
);
CREATE INDEX CoreTracksPrimarySourceIndex ON CoreTracks(ArtistID, AlbumID,
PrimarySourceID, Disc, TrackNumber, Uri);
CREATE INDEX CoreTracksAggregatesIndex ON CoreTracks(FileSize, Duration);
CREATE INDEX CoreTracksExternalIDIndex ON CoreTracks(PrimarySourceID,
ExternalID);
CREATE INDEX CoreTracksUriIndex ON CoreTracks(PrimarySourceID, Uri);
CREATE INDEX CoreTracksCoverArtIndex ON CoreTracks (PrimarySourceID, AlbumID,
DateUpdatedStamp);
CREATE TABLE CoreAlbums (
AlbumID INTEGER PRIMARY KEY,
ArtistID INTEGER,
TagSetID INTEGER,
MusicBrainzID TEXT,
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
ReleaseDate INTEGER,
Duration INTEGER,
Year INTEGER,
IsCompilation INTEGER DEFAULT 0,
ArtistName TEXT,
ArtistNameLowered TEXT,
ArtistNameSort TEXT,
ArtistNameSortKey BLOB,
Rating INTEGER,
ArtworkID TEXT
);
CREATE INDEX CoreAlbumsIndex ON CoreAlbums(ArtistID, TitleSortKey);
CREATE INDEX CoreAlbumsArtistIndex ON CoreAlbums(TitleSortKey,
ArtistNameSortKey);
CREATE TABLE CoreArtists (
ArtistID INTEGER PRIMARY KEY,
TagSetID INTEGER,
MusicBrainzID TEXT,
Name TEXT,
NameLowered TEXT,
NameSort TEXT,
NameSortKey BLOB,
Rating INTEGER
);
CREATE INDEX CoreArtistsIndex ON CoreArtists(NameSortKey);
CREATE TABLE CorePlaylists (
PrimarySourceID INTEGER,
PlaylistID INTEGER PRIMARY KEY,
Name TEXT,
SortColumn INTEGER NOT NULL DEFAULT -1,
SortType INTEGER NOT NULL DEFAULT 0,
Special INTEGER NOT NULL DEFAULT 0,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
);
CREATE TABLE CorePlaylistEntries (
EntryID INTEGER PRIMARY KEY,
PlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL,
ViewOrder INTEGER NOT NULL DEFAULT 0,
Generated INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX CorePlaylistEntriesIndex ON CorePlaylistEntries(PlaylistID,
TrackID);
CREATE TABLE CoreSmartPlaylists (
PrimarySourceID INTEGER,
SmartPlaylistID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
Condition TEXT,
OrderBy TEXT,
LimitNumber TEXT,
LimitCriterion TEXT,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0,
IsHiddenWhenEmpty INTEGER DEFAULT 0
);
CREATE TABLE CoreSmartPlaylistEntries (
EntryID INTEGER PRIMARY KEY,
SmartPlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL
);
CREATE INDEX CoreSmartPlaylistEntriesIndex ON
CoreSmartPlaylistEntries(SmartPlaylistID, TrackID);
CREATE TABLE CoreRemovedTracks (
TrackID INTEGER NOT NULL,
Uri TEXT,
DateRemovedStamp INTEGER
);
CREATE TABLE CoreCacheModels (
CacheID INTEGER PRIMARY KEY,
ModelID TEXT
);
CREATE TABLE CoreShuffles (
ShufflerId INTEGER,
TrackID INTEGER,
LastShuffledAt INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
);
CREATE INDEX CoreShufflesIndex ON CoreShuffles (ShufflerId, TrackID,
LastShuffledAt);
CREATE TABLE CoreShufflers (
ShufflerId INTEGER PRIMARY KEY,
Id TEXT UNIQUE
);
CREATE TABLE CoreShuffleModifications (
ShufflerId INTEGER,
TrackID INTEGER,
LastModifiedAt INTEGER,
ModificationType INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
);
CREATE INDEX CoreShuffleModificationsIndex ON CoreShuffleModifications
(ShufflerId, TrackID, LastModifiedAt, ModificationType);
CREATE TABLE CoverArtDownloads (
AlbumID INTEGER UNIQUE,
Downloaded BOOLEAN,
LastAttempt INTEGER NOT NULL
);
CREATE TABLE IaItems (
ItemID INTEGER PRIMARY KEY,
ID TEXT UNIQUE NOT NULL,
Title TEXT NOT NULL,
MediaType TEXT,
DetailsJson TEXT,
SelectedFormat TEXT,
BookmarkFile TEXT,
BookmarkPosition INTEGER DEFAULT 0
);
CREATE TABLE LastfmStations (
StationID INTEGER PRIMARY KEY,
Creator STRING NOT NULL,
Name STRING NOT NULL,
Type STRING NOT NULL,
Arg STRING NOT NULL,
PlayCount INTEGER NOT NULL
);
CREATE TABLE HyenaModelVersions (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
version INTEGER);
CREATE TABLE PodcastSyndications(last_auto_download INTEGER,AutoDownload
INTEGER,DownloadStatus INTEGER,IsSubscribed INTEGER,FeedID INTEGER PRIMARY
KEY,Title TEXT,Description TEXT,Url TEXT,Keywords TEXT,Category TEXT,Copyright
TEXT,ImageUrl TEXT,UpdatePeriodMinutes INTEGER,Language TEXT,LastDownloadError
INTEGER,LastDownloadTime INTEGER,Link TEXT,MaxItemCount INTEGER,PubDate
INTEGER,LastBuildDate INTEGER,SyncSetting INTEGER);
CREATE INDEX PodcastSyndicationsIndex ON PodcastSyndications (IsSubscribed,
Title);
CREATE TABLE PodcastItems(FeedID INTEGER,LicenseUri TEXT,ItemID INTEGER PRIMARY
KEY,Author TEXT,Comments TEXT,Description TEXT,StrippedDescription TEXT,Guid
TEXT,IsRead INTEGER,Link TEXT,Modified INTEGER,PubDate INTEGER,Title
TEXT,Active INTEGER);
CREATE INDEX PodcastItemsFeedIDIndex ON PodcastItems(FeedID);
CREATE INDEX PodcastItemsGuidIndex ON PodcastItems(Guid);
CREATE INDEX PodcastItemIsReadIndex ON PodcastItems(IsRead);
CREATE TABLE PodcastEnclosures(ItemID INTEGER,EnclosureID INTEGER PRIMARY
KEY,LocalPath TEXT,Url TEXT,Keywords TEXT,Duration INTEGER,FileSize
INTEGER,MimeType TEXT,DownloadedAt INTEGER,DownloadStatus INTEGER);
CREATE INDEX PodcastEnclosuresItemIDIndex ON PodcastEnclosures(ItemID);
CREATE TABLE MetadataProblems (
ProblemID INTEGER PRIMARY KEY,
ProblemType TEXT NOT NULL,
TypeOrder INTEGER NOT NULL,
Generation INTEGER NOT NULL,
Selected INTEGER DEFAULT 1,
SolutionValue TEXT,
SolutionOptions TEXT,
ObjectIds TEXT,
ObjectCount INTEGER,
UNIQUE (ProblemType, Generation, ObjectIds) ON CONFLICT
IGNORE
);
CREATE TABLE Bookmarks(BookmarkId INTEGER PRIMARY KEY,Position
INTEGER,CreatedAt INTEGER,Type TEXT,TrackId INTEGER);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1
VALUES('HyenaModelVersions','sqlite_autoindex_HyenaModelVersions_1','5 1');
INSERT INTO sqlite_stat1 VALUES('LastfmStations',NULL,'6');
INSERT INTO sqlite_stat1 VALUES('CoreRemovedTracks',NULL,'22');
INSERT INTO sqlite_stat1
VALUES('CoreSmartPlaylistEntries','CoreSmartPlaylistEntriesIndex','21040 4208
1');
INSERT INTO sqlite_stat1 VALUES('CoreCacheModels',NULL,'102');
INSERT INTO sqlite_stat1 VALUES('CoreConfiguration',NULL,'19');
INSERT INTO sqlite_stat1
VALUES('CorePrimarySources','sqlite_autoindex_CorePrimarySources_1','12 1');
INSERT INTO sqlite_stat1 VALUES('CoreTracks','CoreTracksCoverArtIndex','13428
2686 14 11');
INSERT INTO sqlite_stat1 VALUES('CoreTracks','CoreTracksUriIndex','13428 2686
1');
INSERT INTO sqlite_stat1 VALUES('CoreTracks','CoreTracksExternalIDIndex','13428
2686 18');
INSERT INTO sqlite_stat1 VALUES('CoreTracks','CoreTracksAggregatesIndex','13428
2 2');
INSERT INTO sqlite_stat1
VALUES('CoreTracks','CoreTracksPrimarySourceIndex','13428 28 15 14 14 2 1');
INSERT INTO sqlite_stat1 VALUES('CoreAlbums','CoreAlbumsArtistIndex','938 2 2');
INSERT INTO sqlite_stat1 VALUES('CoreAlbums','CoreAlbumsIndex','938 2 1');
INSERT INTO sqlite_stat1
VALUES('CoreShufflers','sqlite_autoindex_CoreShufflers_1','1 1');
INSERT INTO sqlite_stat1 VALUES('CoreArtists','CoreArtistsIndex','496 2');
INSERT INTO sqlite_stat1 VALUES('CorePlaylists',NULL,'1');
INSERT INTO sqlite_stat1 VALUES('CoreSmartPlaylists',NULL,'14');
ANALYZE sqlite_master;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
