MYSQL -what is the best practice for checking if multiple phone numbers exist in table

Question

my table contains 5 milion enteries of phone numbers. suppose i need to check if a specific phone numbers exist in that table - i got an input of 1 milion numbers, and need to check what numbers exist in my table - the result has to be under 20 seconds - what is the query for this ?

Answer 1

1. Create one new table for 1 million of phone numbers.

• tableA: 5 million of phone numbers
• tableB: 1 million of phone numbers

2. Create index for tableA's phone numbers field and tableB's field

3. Execute the below SQL script

select 
  tableA.phoneNumber, 
  .... 
from 
  tableA 
where EXISTS(select * from tableB where tableA.phoneNumber=tableB.phoneNumber) 

According to your request, please use the below sql script.

I strongly discourage this because it does not use the index of numbersFromRequest.

SELECT number
FROM 
    (SELECT '0546313243' number
    UNION ALL
    SELECT '0527844910' number
    UNION ALL
    SELECT '0542307563' number
    UNION ALL
    SELECT '0587918462' number
    UNION ALL
    SELECT '0545496228' number ) AS numbersFromRequest
WHERE NOT exists 
    (SELECT 1 from PhoneNumber
    where PhoneNumber.number = numbersFromRequest.number)

Also, I create some data for your testing. link

Like this as below.

CREATE TABLE numbersFromRequest
  (number varchar(20), INDEX USING BTREE (number))
  ENGINE = MEMORY;

INSERT INTO numbersFromRequest(number)
SELECT '0546313243' number
UNION ALL
SELECT '0527844910' number
UNION ALL
SELECT '0542307563' number
UNION ALL
SELECT '0587918462' number
UNION ALL
SELECT '0545496228' number

And then you can use temp table.