Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

subquery or join?

Posted on 2014-03-22
8
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 41

Expert Comment

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

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
 
LVL 1

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 25

Assisted Solution

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

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

721 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