3

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.

3

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

| improve this answer | |
0

I think you've confused some syntax.

Best way to do this is

SELECT My_Fields[,,,,] FROM My_Table WHERE number LIKE '123%' OR number LIKE '456%';

This will work for VARCHARs, but not for INTEGERs. But, '%LIKE%' doesn't exist, unless you're trying to pick out the string 'LIKE' from a longer string.

A search for the string - LIKE %123% makes no sense - it has to be between two single quotes - i.e. like LIKE '%123%';

If the string you're searching for is "embedded" in the phone number string - i.e. it's VARCHAR(25) - or similar - and you're searching for 123 in '01-24-123...', then your search string is non-sargable which can lead to significant performance hits.

To search within the string, use LIKE '%123%' - but, as I've said, it will not use indexes - check out the URL I've referenced for sargable.

All of this should be carefully analysed before system launch - unless you have only a couple of thousand records.

I was in College and the Library had introduced a new cataloguing system - you could be there minutes before a search came up for this very reason, - I told the librarian that this was a basic mistake and that the people who designed the system should be shot - she replied, "No, that would be too good for them!" :-)

| improve this answer | |
  • 1
    I think you confuse the question – paparazzo Jul 14 '16 at 23:16
  • Show me where I am confused? My remarks about being sargable seem to be what you're driving at. I don't see how a second table is relevant though - that the query is sargable or not is a completely separate matter from different tables. – Vérace Jul 14 '16 at 23:59
  • Well for one that query does not even return the proper results – paparazzo Jul 15 '16 at 0:05
  • You're being disingenuous - I made it clear (from the URL I referenced) that there is a huge difference between searching for '123%' and '%123%' - the first is sargable, the second is not. I've made that more explicit in an edit to my post - but I fail to see how all of this is relevant to your post. Your post about searching for "= '1234' is a complete red herring, doesn't answer the question and is misleading. Searching for an exact match on '1234' is meaningless, unless the string is "1234". If it begins with 1234, then it's '1234%', but the OP seems to want to search more generally. – Vérace Jul 15 '16 at 0:56
  • 1
    U can read the question how you chose. I read select userID from table where number1=1234 or number2=1234 – paparazzo Jul 15 '16 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.