how to query by checking if specific fields start with a value from a given array?

user_10843638 0
Viewed 45

(MySQL) I have a query to check if 'phone_number' or 'fax_number' startsWith a value from a given array,

lets say const possibleValues = [123,432,645,234]

currently my query runs with the 'or' condition, to check if -

'phone_number' or 'fax_number' that starts with 123 
or 
'phone_number' or 'fax_number' that starts with 432
or
'phone_number' or 'fax_number' that starts with 645
or
'phone_number' or 'fax_number' that starts with 234

it runs extremely slow on a big database, and I wish to make it faster, is there a way to make it run faster?

I'm kinda new to sql queries,

any help would be highly appreciated!

mysql sql
4 Answers

You can try something like:

SELECT * FROM table_1 
WHERE CONCAT(',', `phone_number`, ',') REGEXP ',(123|432|645|234),'  
OR CONCAT(',', `fax_number`, ',') REGEXP ',(123|432|645|234),';

Demo

user_164619520

Try creating an in-line table and join with it.

WITH telnostart(telnostart) AS (
          SELECT '123'
UNION ALL SELECT '432'
UNION ALL SELECT '645'
UNION ALL SELECT '234'
)
SELECT
  *
FROM your_search_table
JOIN telnostart ON (
   LEFT(tel_number,3) = telnostart
OR LEFT(fax_number,3) = telnostart
user_67882550

you can use a case statement to add a flag column

select *
,case when left(phone_number,3) in (123,432,645,234) or left(fax_number,3) in (123,432,645,234) then 1 else 0 end as contact_check_flag 
from table_name

As per your requirement, you can filter it or use it elsewhere.

user_108432000 
SELECT * FROM table_1 
    WHERE `phone_number` REGEXP '^(123|432|645|234)'  
       OR `fax_number`   REGEXP '^(123|432|645|234)';

But it won't be fast. (And no regular INDEX will help.)

If there phone numbers are spelled out like in the US: "123-456-7890", then you could use a FULLTEXT(phone_number, fax_number) index and

SELECT * FROM table_1 
    WHERE MATCH(phone_number, fax_number)
          AGAINST('123 432 645 234');

This is likely to be much faster, but not as "general".

user_17668310
Related