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.