Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# subquery or join?

Posted on 2014-03-22
Medium Priority
271 Views
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
Question by:myyis

LVL 41

Expert Comment

ID: 39948078
Can you explain what are you trying to achieve with some data?
0

LVL 1

Author Comment

ID: 39948450
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

LVL 1

Author Comment

ID: 39948527
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

LVL 26

Assisted Solution

Tomas Helgi Johannsson earned 1000 total points
ID: 39948540
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

LVL 13

Accepted Solution

magarity earned 1000 total points
ID: 39948679
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

LVL 1

Author Comment

ID: 39948685
MySQL does have a computed column?
0

LVL 13

Expert Comment

ID: 39948689
Argh!  I thought I was looking through the SQL Server section, duh!  Sorry!  But the principle applies. Does MySQL not have computed columns?
0

LVL 13

Expert Comment

ID: 39954412
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month12 days, 6 hours left to enroll