Unit 5 uttered:
--- Robert Simpson <[EMAIL PROTECTED]> wrote:
You need to create an index on the columns you're
joining. Otherwise I
believe 100,000,000 rows (10k x 10k) in table 2 will
be scanned while SQLite
looks for matches against the 10,000 rows in table
1.
While that makes sense, I suspect there is something
else going on.
I did a few more tests. For example, if I remove the
INSERT but keep the exact same SELECT statement with
the joins, it is fast again. So, it seems that it is
quite slow when doing the insert's. I was thinking
that perhaps the statement was not in a transaction,
but I tried that too.
Try both the INSERT and the plain SELECT using EXPLAIN QUERY PLAN, which
will give an indication of which indexes are being used. I'd hazard a
guess that the INSERT case is not using the same query plan as the plain
select case.
sqlite> EXPLAIN QUERY PLAN INSERT ... SELECT ... FROM table-1 JOIN table-2 ...
sqlite> EXPLAIN QUERY PLAN SELECT ... FROM table-1 JOIN table-2 ...
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
