Is “%LIKE%” faster than executing multiple queries

database-design

for example, each user on my site has 2 phone numbers (i.e. let say 1234 and 6789) .

but about performance , which is faster:

to save the numbers in one column(separated by comma,like [1234,6789] ), and thus, searching the columns with :

select  userID from table where number LIKE %1234%

or saving values into 2 columns

select  userID from table where number1=1234 or number2=1234

which will be faster? lets say i have thousands or millions of rows.

Best Answer

This is bad - it is a table scan 

LIKE %1234

But thousands of rows is not much

Best would be to have second table with phone 

phone:  
userID PK FK to user   
number PK



select * 
from user 
join phone 
       on phone.userID = user.userid 
      and phone.numer = '1234'

if you don't want to fix the data design then two column with = will be faster

Related Question