MySQL Query – How to Check if All Elements of an Array Exist in a Table

existsMySQLperformance

Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,… 392] where n is bigger than 100k entries. Some of this entries are repeated.

I need to check if all of these registers are contained in a MySQL table containing more than 5M entries.

Now I'm checking one after another, but this process takes a very long time.

How can I make this verification in one single check, or maybe make this more efficient.

I would like to know if all of them exists on the table, and, if possible, know which don't already exists on that table.

Best Answer

First count the number of distinct numbers. You have called it n. Then

SELECT COUNT(*) FROM tbl
    WHERE id IN (very-long-list)

See if that returns n.

If, instead, you have put those numbers in another table, then this might be optimal:

Table `nums` contains the `n` values.
ALTER TABLE tbl ADD INDEX(id);  -- if it does not already exist.
SELECT ! EXISTS ( SELECT * FROM nums LEFT JOIN tbl USING(id)
                      WHERE tbl.id IS NULL )

That will check each nums.id to see if it is in your tbl. More importantly it stops when the first missing item is found. The output is 1 or 0.

Related Question