Solved

subquery or join?

Posted on 2014-03-22
8
259 Views
Last Modified: 2014-03-25
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
Comment
Question by:myyis
8 Comments
 
LVL 40

Expert Comment

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

Author Comment

by:myyis
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
 

Author Comment

by:myyis
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 250 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

by:
magarity earned 250 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
 

Author Comment

by:myyis
ID: 39948685
MySQL does have a computed column?
0
 
LVL 13

Expert Comment

by:magarity
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

by:magarity
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question