INSERTing rows that is not in a table
I'm using this query but it is really really slow
INSERT INTO a (b,c,d,e,f,g,h,i) SELECT b,c,d,e,f,g,h,i FROM z WHERE b NOT IN (SELECT b FROM a)
What is does is find all records where b is not in table "a" from table z and importing it into table a.
Its really really slow and keeps timing. Is there away to make it quicker?
Thank-you BigThin开发者_StackOverflow中文版gs
P.s.
Make the b
column unique, then INSERT
with the IGNORE
option, so:
INSERT IGNORE INTO a (b,c,d,e,f,g,h,i)
SELECT b,c,d,e,f,g,h,i FROM z
INSERT INTO a (b,c,d,e,f,g,h,i) SELECT a.b,a.c,a.d,a.e,a.f,a.g,a.h,a.i FROM z,a WHERE z.b != a.b
INSERT INTO a (b,c,d,e,f,g,h,i) SELECT b,c,d,e,f,g,h,i FROM z WHERE NOT EXISTS (SELECT 1 FROM a WHERE z.b = a.b)
Use this simple trick:
INSERT INTO a (b,c,d,e,f,g,h,i)
SELECT b,c,d,e,f,g,h,i
FROM z
LEFT JOIN a on a.b = z.b
WHERE a.b IS NULL;
You'll only get a row when there isn't a matching row in b
, and the query will be able to use indexes effectively.
精彩评论