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
