Solved

SQL optimization

Posted on 2013-01-14
2
181 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
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 142

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now