Solved

SQL optimization

Posted on 2013-01-14
2
187 Views
Last Modified: 2013-01-18
Hello,
  I have a table that contain phone numbers. This table is very large and has an index on ph#.

I have an application that users can query for data using the ph#. They should be able to enter partial phone  #

my application has a  text box for users to enter the ph# and a combox with the following [equal ,.startwith, endWith]. The phone number is not in a standard format

here an example of the table
IDx PH
1     9243333
2     53333
3     3333378
4     923-1334



Suppose a user is looking for all information where the  phone#  endwith 3333 . How would I write this query ?. The result should be index = 1 ,2]

I am using SQLITE DB
0
Comment
Question by:SiemensSEN
[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
2 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 150 total points
ID: 38778450
The "equal" query will be very fast if there is an index on the phone number. So I would try that first and if there is a hit, return those records. If you must ALWAYS do a "end with" then you're going to be very slow no matter what. I would suggest that you not make that an always function, maybe allow the user to have an "exact match" radio button and a "contains" button. And you have to use WHERE PH LIKE '%'+@whatuserinput+'%' to do partial phone number searches.

EDIT: one more tip, if you can make it where they have to select radio button:

() equals () starts with () ends with

Then you can have 3 queries, the top being fastest:

WHERE PH = @userinput

WHERE PH LIKE @userinput+'%'

WHERE PH LIKE '%'+@userinput

And if the data is clustered index on PH then the 2nd WHERE will be pretty fast also.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 38782414
> If you must ALWAYS do a "end with"

you could then store, besides the actual phone number, the "reversed phone number", index that, and search by the reserves string also.
 
Dx PH     PHr
1     9243333   3333429
2     53333      33335
3     3333378      8733333
4     923-1334    4331329

Open in new window

with index on PHr, this query will be quite fast:
SELECT * from yourtable WHERE PHr LIKE '3333%' 

Open in new window

0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

626 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