On Thu, Oct 23, 2014 at 4:03 PM, Tomislav Ljubej <spor...@gmail.com> wrote:
> 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); > Thanks. That was sufficient for me to reproduce the problem. The problem is caused by this check-in: http://www.sqlite.org/src/info/0bdf1a086b And that check-in was necessary to ensure correct behavior in certain obscure circumstances. So I cannot easily back it out. But I can work to try to find a different optimization that makes your query run faster. In the meantime, may I suggest rewriting your query. The query you have is this: INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, CoreTracks.TrackID FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks WHERE CoreTracks.Year IN (SELECT CoreTracks.Year FROM CoreTracks, CoreCache WHERE CoreCache.ModelID = 71 AND CoreCache.ItemID = CoreTracks.TrackID ) ORDER BY Year; I suggest rewriting it as follows: INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, MIN(TrackID) FROM CoreTracks GROUP BY Year HAVING Year IN (SELECT CoreTracks.Year FROM CoreTracks, CoreCache WHERE CoreCache.ModelID = 71 AND CoreCache.ItemID = CoreTracks.TrackID ) ORDER BY Year; Or perhaps this: INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, MIN(TrackID) FROM CoreTracks WHERE Year IN (SELECT CoreTracks.Year FROM CoreTracks, CoreCache WHERE CoreCache.ModelID = 71 AND CoreCache.ItemID = CoreTracks.TrackID ) GROUP BY Year ORDER BY Year; In 3.8.6, SQLite was making the above transformation automatically. But there are corner cases where this transformation is not valid and so it was disabled for 3.8.7, which is apparently what is causing your slowdown. No promises, but I will try to make this transformation occur automatically again for SQLite 3.8.8, at least in your case where it does appear to be valid. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
