Maybe the original intent was to count all the rows in all the tables separately and return a vector of record counts, as a poor man's integrity check to make sure no rows got lost. Like
Select (select count() from t1) as t1,(select count() from t2) as t2, ...; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Richard Hipp Gesendet: Dienstag, 14. Februar 2017 17:04 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] sqlite3 hangs on query On 2/14/17, Igor Tandetnik <i...@tandetnik.org> wrote: > On 2/14/2017 7:33 AM, Jens-Heiner Rechtien wrote: >> please consider the attached dump of a sqlite3 database and the >> following - admittedly nonsensical, don't ask - query over a restored >> version of the database: >> >> *SELECT count(*) FROM spacefavorite, album_asset, albums, >> assetProfileLinks, avatarCacheReferences, cacheReferences, comment, >> conflicts, coreInfo, coreMD5, errors, flags, importSource, >> missingBinariesOnOz, profileRegistration, quota_exceeded, >> renditionRevisions, space, space_album LIMIT 1;** >> * > > You are asking SQLite to count an enormous number of rows, so don't be > surprised if that takes an enormous amount of time. > > You have a cross-join of 19 tables. Even if each one contains just 2 > rows, that's 2^19 ~ 500K rows to work through. And it grows > exponentially from there. I suppose a query planner optimization is possible here. SQLite could rewrite queries of the form: SELECT count(*) FROM t1,t2,t3,t4,...,tN; Into something like this: SELECT (SELECT count(*) FROM t1)*(SELECT count(*) FROM t2)*...*(SELECT count(*) FROM tN); I say that it is possible to do this. But it seems like a low-value optimization - just something to complicate testing and increase the library footprint without actually adding value. So there is nothing like this on the To-Do list. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
