On Jul 25, 2005, at 6:06 AM, David Fowler wrote:
On 2005-07-25 at 10:58:04 [+0200], David Fowler
..
===================================================
Thanks for the pointer Charlie, but I was only using * for my example,
and I normaly use fully named columns (table.column) when writing
queries.
My new query, looks like this (generified):
SELECT count(*) FROM table1
INNER JOIN table2 ON (table1.id = table2.rel_id)
INNER JOIN table3 ON (table3.rel_id = table2.id)
INNER JOIN table4 ON (table3.id = table4.rel_id)
INNER JOIN table5 ON (table5.rel_id = table4.id)
WHERE table1.value = 'value1'
AND ((table3.value LIKE '%value3%' AND table5.value = 'value5')
OR (table3.value LIKE '%value3%' AND table5.value = 'value5'));
This query executes in 0.02s on MySQL, but about 14s on SQLite (to
return 34 rows).
I think the problem is the size of the temporary table the JOIN
creates (37,434) which is probably the issue. There is (7,579) records
in my largest table. I'm not sure what my best option would be to get
this size down. But I'm thinking along the lines of some nested
SELECTs to get needed rows in stages rather than all in one go at the
end. My application is probably going to get much larger data wise
than it already is and MySQL is not really an option anymore. Could
possibly go back to UNIONs also if the OR operation isn't as efficient
as two SELECTs (though I highly doubt that is the case).
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
