Might I suggest the following --
make your query in steps, and note where it actually starts slowing down.
For example,
Step 1.
SELECT COUNT(*)
FROM t1
(took a fraction of a pico second... good)
Step 2.
SELECT COUNT(*)
FROM t1
WHERE t1.c1 = 'c1'
(took a couple of fractions of a pico second... still good)
Step 3.
SELECT COUNT(*)
FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2
WHERE t1.c1 = 'c1'
(still just a couple of fractions of a pico second... still good)
and so on... you get the picture. SQL gets complicated very rapidly, and
only by building it step by step can we optimize it best.
That will pin-point for you where the query goes haywire. Then you can
concentrate on fixing that. Perhaps you are not using your indexes, perhaps
you don't have indexes, whatever. Then you can come back and ask for help on
the specific problem. Maybe the SQLite designers might even discover a
legitimate place for improvement in their code, and then everyone will
benefit.
Comparing two databases is pointless because they all do things so
differently internally even though we as users expect the same external
results.
--
Puneet Kishor
=============================================
Thanks for the pointer, I was just doing this myself (not intentionally
though) by constructing my query out of nested SELECTs rather than JOINs and
I noticed that the inner SELECTs were taking fractions of seconds, what I
was doing in the next level up was taking a lot longer. I think the reason
for it is table1 and table2s JOIN created alot of rows, no matter what
conditions I placed in the ON statement, because table1 was so large and
none of the conditions applied to it in a manner that made it more
efficient. I guess MySQLs optimizer is really doing its job, and I need to
do the optomising myself for SQLite.
Thanks for the input Puneet.
