I remember why I added the sqlite_stat2 flag together with the ANALYZE command.
It makes certain illformed queries efficient.
CREATE TABLE IF NOT EXISTS TC -- 10 rows
(
C INTEGER PRIMARY KEY,
Y CHAR(255) NOT NULL UNIQUE,
);
CREATE TABLE IF NOT EXISTS TB -- 1000000 rows
(
B INTEGER PRIMARY KEY, -- primary key!
X CHAR(255) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS TA -- 10 rows
(
A INTEGER PRIMARY KEY,
C INTEGER NOT NULL,
B INTEGER NOT NULL,
UNIQUE (C, B) -- unique together with C!
);
Now, consider this query:
SELECT * FROM TB NATURAL JOIN TA; -- slow
vs
SELECT * FROM TA NATURAL JOIN TB; -- fast
That's why I needed the statistics. Otherwise, SQLite failed to select the best
index. I understand it would choose the primary key in this case, which is of
course wrong.
(I haven't tested the above for a while now, but I remember the problem I
encountered had something to do with those tables.)
Regards
> From: [email protected]
> Date: Tue, 8 Feb 2011 16:53:52 +0000
> To: [email protected]
> Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
>
>
> On 8 Feb 2011, at 4:22pm, Sven L wrote:
>
> > Thank you very much for your detailed explanation!
>
> You're welcome. I've actually never had to think out this feature of SQLite
> before, so it was interesting for me too. I hope Richard or the rest of the
> team will correct me if I got anything wrong.
>
> > I will comment out my calls to ANALYZE, and see how my software performs.
> >
> > The reason why I added it in the first place is that users are allowed to
> > create their own queries, and since not all of them are SQL experts, I
> > wanted the engine to be as tolerant as possible. Perhaps it's a better idea
> > to simply crave decent SQL!
>
> Allowing users to make up their own queries on the fly does make things a
> little more difficult as you worked out: you can't pre-make good indexes. The
> big server/client databases cache temporary indexes (and share them between
> users), so they handle unexpected queries far better: if any query comes in
> that doesn't suit any indexes it simply makes up a new temporary index and
> keeps it in case it's needed later. SQLite can't do this because it's
> designed for a tiny footprint and can't chew up lots of memory or disk space
> without a good reason.
>
> But your initial questions did sound a little like premature optimisation and
> I think you'll get decent results without worrying too much about it. I'm
> sure the overwhelming number of SQLite users have never used ANALYZE even
> once.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
