How to quickly check if a record exists in a table?

Question

MySQL

There are two tables, table A with a huge amount of data and an empty table B. The task is to write the transferred data to table B, provided that one field does not exist in table A. Now a search has been made and a request for each element in the loop SELECT 1 FROM (`А`) WHERE `поле` = '123' LIMIT 1. I made the `field` field a unique index, removed the data acquisition by replacing * with 1. Tell me how else can I speed up this process?

Answer 1

cache in memcache

Answer 2

Well, for example, COUNT() is much faster. If you do not choose, but check, then it will be faster

Answer 3

I didn't really understand, but maybe you need to get everything that is not in A with one request? For example, a link from

create temporary table temptable...
insert into temptable values ()()()...
select * from temptable where not exists (select * from a where a.field = temptable.field)
drop temptable

Answer 4

"on the forehead" we get records that are missing in another table and shove them into b

insert into b (.....)
select * from temp where temp.field not in (select distinct field from a)

Kind of "better"

insert into b (.....)
select * from temp
left join a on temp.field=a.field
where a.field is null

In principle, a normal optimizer will execute both the first and second at the same speed