• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

subquery or join?

Which has better performance?

1. SELECT * FROM CONTACTPHONE CE
LEFT JOIN CALLS CL ON SUBSTR(replace(replace(replace(replace(CE.NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=SUBSTR(CL.NUMBER, -10, 10) AND CE.ORID=CL.ORID

2. SELECT * FROM CONTACTPHONE CE
LEFT JOIN CALLS CL ON SUBSTR(replace(replace(replace(replace(CE.NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=(SELECT SUBSTR(CL.NUMBER, -10, 10) AND CE.ORID=CL.ORID

Thank you.
0
myyis
Asked:
myyis
2 Solutions
 
SharathData EngineerCommented:
Can you explain what are you trying to achieve with some data?
0
 
myyisAuthor Commented:
The table  CONTACTPHONE is a list of customers with phone numbers.

Table CALLS is filled  when an outbound call is received. We get the number calling but we don't know whose number it is.

So the problem is reaching the customer name using the numbers in CALLS table
0
 
myyisAuthor Commented:
The 2nd alternative should be like this, I misedited, sorry for that

2. SELECT * FROM CONTACTPHONE
WHERE SUBSTR(replace(replace(replace(replace(NUMBER,' ',''),'-',''),'(',''),')',''), -10, 10)=(SELECT SUBSTR(NUMBER, -10, 10)  FROM CALLS )
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Tomas Helgi JohannssonCommented:
Hi!

Issue a explain on both queries and post them here.

http://dev.mysql.com/doc/refman/5.6/en/explain.html
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Depending on those result you will quickly see what query has the best performance. And also you will see if there is a need for extra indexes to aid your query performance.

Regards,
   Tomas Helgi
0
 
magarityCommented:
I suspect the optimizer will get either identical or nearly identical plans for both queries. Both will be bad. The substring functions are going to make it into table scans.  Since there is also the replace function, I doubt even a text index would help.
If you're having performance problems and this is an important query I suggest you look into making a persisted computed column and index it and then use that in your join instead.  There is some minor overhead to the function but it's not bad if you need it.
Here's the overview of computed columns on technet to get you started: http://technet.microsoft.com/en-us/library/ms191250(v=SQL.105).aspx
Here is the overview of creating an index on one:
http://technet.microsoft.com/en-us/library/ms189292.aspx

PS - Your question is filed generically under "SQL Server" and not any particular version. You may not be able to do this with an older version.
0
 
myyisAuthor Commented:
MySQL does have a computed column?
0
 
magarityCommented:
Argh!  I thought I was looking through the SQL Server section, duh!  Sorry!  But the principle applies. Does MySQL not have computed columns?
0
 
magarityCommented:
Thanks for the points and sorry again about the MS vs MY SQL confusion.  I assume you found out how to make a computed column in your MY SQL?
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now